How to rank numbers sequentially by value

[Salgueiro]

Hello, new member but longtime reader.
I haven't been able to find a solution for this particular issue, hope there is one.

Given a sheet with several thousand lines, each line representing an item reference code (SS0003, SR0528, etc), I've added a simple countif on each line to determine how many equal reference codes are in the sheet. For example, each "SS0003" reference code has 18 entries, "SR0528" has 17, etc.

What I need to do is rank the numbers from 1 to 10 (I'm trying to build a Top 10 without having to do a pivot table), but using unique values. That is, 18 would be 1 (as it's the largest value), 17 would be 2 (as it's the second largest value), and so on. I've tried the RANK function, but I find that 18 is indeed 1, but the second highest value, 17, is actually ranked 19 (as there are 18 values before it).

Is there a way? I hope I've explained myself properly, and thanks for your help.

 

[Aladin Akyurek]

Care to post the COUNTIF formula that you have?

 

[Salgueiro]

Care to post the COUNTIF formula that you have?

Hi,

the COUNTIF is not really very relevant. Something like COUNTIF(A:A;"A2") where the values in column A are the references. So it there are 18 "SS0003" and 17 "SR0528, it ends up like this:

References	Countif(A:A;A2)	RANK(A2;A:A)
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SS0003	18	1
SR0528	17	19
SR0528	17	19
SR0528	17	19
SR0528	17	19
SR0528	17	19

<colgroup><col><col><col></colgroup><tbody>
</tbody>

My question is: is there a way for excel to identify 18 as the highest value in column C, therefore 1, and 17 as the

second highest value in column C, therefore 2 (and not 19)?

 

[Salgueiro]

Actually, the RANK formula would be RANK(B2;B:B). Sorry about the mix-up

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=MATCH(B2,LARGE(IF(FREQUENCY($B$2:$B$24,$B$2:$B$24),$B$2:$B$24),ROW(INDIRECT("1:"&SUM(IF(FREQUENCY($B$2:$B$24,$B$2:$B$24),1))))),0)

 

[Salgueiro]

Hi,

sorry for the late reply, and thank you for the formula.

 

[Aladin Akyurek]

Hi,

sorry for the late reply, and thank you for the formula.

You are welcome. Thanks for providing back