Need help creating a dynamic Top Ten list

S

SammySpaceman

Board Regular
Joined
Aug 18, 2002
Messages
64
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a list of titles in Column A with the corresponding number of plays in Column HG. What I am trying to do is create a Top Ten List of most played titles. I'd like it to be dynamic; so, as more plays are accrued throughout, the list adjusts accordingly.

In a different sheet, I use Large to figure out the top ten number of plays, and input those values into the B Column, starting with B75. I.e. =LARGE('Full Stats'!$HG$3:$HG$98,1)

Then, I used Index/Match to locate the corresponding title for those plays, and input those values into the A Column, starting with A75. I.e. =INDEX('Full Stats'!$A$3:$A$98,MATCH(B75,'Full Stats'!$HG$3:$HG$98,0)))

That worked until I encountered duplicate values for number of plays. To return each title that had a duplicate number of plays, I used this formula: =IFERROR(INDEX('Full Stats'!$A$3:$A$98, SMALL(IF($B$78='Full Stats'!$HG$3:$HG$98, ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1), ROW(1:1))),"" )

Additionally, I was able to extend it across the number of cells that had duplicate values.

At that point, I thought I might be able to use IF to combine the features to create the dynamic part of the list. The reason for this is that the values won't always match, and, when they do match, there could be two matches or three or even four. So, I tried using the below formula to evaluate the value in Column B with the one below it to see if those number of plays would match. My logic was that if they did match, I might be able to use the Index/Small formula listed above, and, if they didn't match, I could use the Index/Match formula listed above.

Here's the formula I tried: =IF(B79=B78,INDEX('Full Stats'!$A$3:$A$98, SMALL(IF($B$78='Full Stats'!$HG$3:$HG$98, ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1), ROW(1:1))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B78,'Full Stats'!$HG$3:$HG$98,0)))

The weird thing is that when I click on the Function Argument, the Formula result listed is the correct title. However, once I click OK, the title displayed in the cell is not the correct one.

Any help is greatly appreciated.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
SammySpaceman said:
The weird thing is that when I click on the Function Argument, the Formula result listed is the correct title. However, once I click OK, the title displayed in the cell is not the correct one.
Click to expand...
A very quick observation - I have only read this bit! I haven't even looked at your formula.

Your comment suggests that you haven't array-entered the formula, i.e. CTRL-SHIFT-ENTER rather than just ENTER.
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

A small set of sample dummy data and expected results would also help if your problem remains unresolved by post #2.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Stephen,

I didn't realize I had to array-enter the formula every time. That adjustment did cause the correct title to be displayed.

Unfortunately, I still haven't figured out how to make the top ten list dynamic. I can get the correct titles to appear, by manually changing the array formulas in each cell, but that kind of defeats the point.

Peter,

Per your suggestion, I updated my account details--Excel 2019/Windows. I've included a snippet of what I have below, as well.

Thanks!

Mastermind and Scheme - Win-Loss Record.xlsx
AB
75Horror of Horrors9
76Five Families of Crime8
77Dark Reign of H.A.M.M.E.R. Officers7
78#NUM!6
79#NUM!6
80#NUM!5
81#NUM!5
82#NUM!4
83#NUM!4
84Avengers vs. X-Men3
Most Played Masterminds
Cell Formulas
RangeFormula
A75:A84A75=IF(B76=B75,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B75='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:1))),IF(B75=B74,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B75='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:1))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B75,'Full Stats'!$HG$3:$HG$98,0))))
B75B75=LARGE('Full Stats'!$HG$3:$HG$98,1)
B76B76=LARGE('Full Stats'!$HG$3:$HG$98,2)
B77B77=LARGE('Full Stats'!$HG$3:$HG$98,3)
B78B78=LARGE('Full Stats'!$HG$3:$HG$98,4)
B79B79=LARGE('Full Stats'!$HG$3:$HG$98,5)
B80B80=LARGE('Full Stats'!$HG$3:$HG$98,6)
B81B81=LARGE('Full Stats'!$HG$3:$HG$98,7)
B82B82=LARGE('Full Stats'!$HG$3:$HG$98,8)
B83B83=LARGE('Full Stats'!$HG$3:$HG$98,9)
B84B84=LARGE('Full Stats'!$HG$3:$HG$98,10)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
If I adjust the Row data in the forumula for in each of the cells in Column A that have #NUM!, then, I can achieve the below results.

Mastermind and Scheme - Win-Loss Record.xlsx
AB
75Horror of Horrors9
76Five Families of Crime8
77Dark Reign of H.A.M.M.E.R. Officers7
78Crush Them with My Bare Hands6
79Predict Future Crime6
80Nuclear Armageddon5
81United States Split by Civil War5
82Pull Reality into the Negative Zone4
83Transform Citizens into Demons4
84Avengers vs. X-Men3
Most Played Masterminds
Cell Formulas
RangeFormula
A84,A75:A77A75=IF(B76=B75,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B75='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:1))),IF(B75=B74,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B75='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:1))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B75,'Full Stats'!$HG$3:$HG$98,0))))
B75B75=LARGE('Full Stats'!$HG$3:$HG$98,1)
B76B76=LARGE('Full Stats'!$HG$3:$HG$98,2)
B77B77=LARGE('Full Stats'!$HG$3:$HG$98,3)
A78:A79A78=IF(B79=B78,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B78='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:4))),IF(B78=B77,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B78='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:4))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B78,'Full Stats'!$HG$3:$HG$98,0))))
B78B78=LARGE('Full Stats'!$HG$3:$HG$98,4)
B79B79=LARGE('Full Stats'!$HG$3:$HG$98,5)
A80:A81A80=IF(B81=B80,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B80='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:6))),IF(B80=B79,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B80='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:6))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B80,'Full Stats'!$HG$3:$HG$98,0))))
B80B80=LARGE('Full Stats'!$HG$3:$HG$98,6)
B81B81=LARGE('Full Stats'!$HG$3:$HG$98,7)
A82:A83A82=IF(B83=B82,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B82='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:8))),IF(B82=B81,INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B82='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:8))),INDEX('Full Stats'!$A$3:$A$98,MATCH(B82,'Full Stats'!$HG$3:$HG$98,0))))
B82B82=LARGE('Full Stats'!$HG$3:$HG$98,8)
B83B83=LARGE('Full Stats'!$HG$3:$HG$98,9)
B84B84=LARGE('Full Stats'!$HG$3:$HG$98,10)
Press CTRL+SHIFT+ENTER to enter array formulas.


However, as I previously stated, this method forces me to manually adjust the formulas. My goal is to have formulas that can take into account whether there are duplicate values, and, if so, to provide each of the corresponding matches. If there aren't duplicate values, then, I'd just like the single corresponding match.
 
Upvote 0
Thanks for updating your details. (y)

2019 means that you should not need to use array-entered or individually adjusted formulas for this. Try these standard-entry formulas, copied down.

SammySpaceman.xlsm
AB
75Horror of Horrors9
76Five Families of Crime8
77Dark Reign of H.A.M.M.E.R. Officers7
78Crush Them with My Bare Hands6
79Predict Future Crime6
80Nuclear Armageddon5
81United States Split by Civil War5
82Pull Reality into the Negative Zone4
83Transform Citizens into Demons4
84Avengers vs. X-Men3
Most Played Masterminds
Cell Formulas
RangeFormula
A75:A84A75=INDEX('Full Stats'!A:A,AGGREGATE(15,6,ROW('Full Stats'!A$3:A$98)/('Full Stats'!HG$3:HG$98=B75),COUNTIF(B$75:B75,B75)))
B75:B84B75=LARGE('Full Stats'!$HG$3:$HG$98,ROWS(B$75:B75))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question Question
Token replace between two files with dynamic refresh
Replies
1
Views
87
Rijnsent
Rijnsent
S
  • Solved
Need help using Index/Match to return multiple qualifying values
Replies
5
Views
852
KRice
K
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
598
StephenCrump
S
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
374
Trixterz
T
H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
206
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top