Ranking Formula

SanFelippo

Board Regular
Joined
Apr 4, 2017
Messages
124
Hello,

I have a list of Data with the following headers: Count, Year, ID.

What I am trying to do is come up with a formula that will give me the top 25 IDs for each year that have the highest counts. Basically the table I want to fill out looks like what you see below. I need a formula that will find and return the ID of each rank for each year. Any ideas?


Year
20072008200920102011201220132014201520162017
Rank1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<colgroup><col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;" span="13"> <tbody> </tbody>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Maybe something like


Excel 2013/2016
ABCDEFGHIJKLMNOP
1CountyearIDRank20072008200920102011201220132014201520162017
2222007abc11abc4abc4abc4abc3abc3abc4abc3abc3abc4abc3abc3
392007abc22abc3abc3abc3abc4abc3abc4abc2abc2abc3abc2abc2
4312007abc33abc1abc1abc1abc1abc1abc2abc1abc4abc4abc4abc4
51392007abc44abc2abc2abc2abc2abc2abc1abc1abc1abc2abc2abc1
6142008abc1
7102008abc2
8442008abc3
9652008abc4
10162009abc1
1172009abc2
12472009abc3
13672009abc4
Sheet1
Cell Formulas
RangeFormula
F2{=INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45),$E2),$A$2:$A$45,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Fill formula down & across
 
Upvote 0
Sorry, I may have spoken too soon. It appeared to be workingat first, but some of the rankings aren’t correct. For example, in 2014 I havean ID that should be in the rank 20 spot, but it isn’t showing up at first.However, when I sort the data I have be year largest to smallest, it correctlyplaces the ID at rank 20 but messes up some others that were correct before.Any idea why it would be doing that?

 
Upvote 0
Apologies, my mistake.
If you have the same count in different rows, that formula will pull out the first one, which may not be correct.
I'll have another look tomorrow if nobody else steps in.
 
Upvote 0
I believe the above formula can create invalid results in the case of duplicate amounts. For example, if A7 is 139 in the example above, the 2008 list will be off.

Duplicates in different years can be solved with this variation of the formula:

=INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45),$E2)&"|"&F$1,$A$2:$A$45&"|"&$B$2:$B$45,0))
with Control+Shift+Enter.


It's even trickier if there are duplicates within a year, for example if you put 31 in A3 and A4. This formula should work for that, but it does assume that the values in column A are integers, and that the number of rows is less than 1000:

=INDEX($C$2:$C$45,MATCH(LARGE(IF($B$2:$B$45=F$1,$A$2:$A$45+ROW($A$2:$A$45)/1000),$E2),$A$2:$A$45+ROW($A$2:$A$45)/1000,0))

with Control+Shift+Enter.
 
Upvote 0
The number of rows in this unfortunately is 12,377. There could be the case where there are duplicates within a year, but it should be a very rare occurrence. Is there a reason it has to be less than 1000, or was that just the number you went with for the formula because I hadn’t given you specifics?
 
Last edited:
Upvote 0
Just I number I picked since I didn't have specifics. You should be able to change the 1000's I marked in red to 100000's (a number bigger than 12,377) and it should work.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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