Eliminate Duplicates In Rank Formulas

Hey Everyone,

I'm in a real conundrum, and I'm really hoping one of the experts here can help me out!!



In a nut shell, I have a sheet with a whole ton of values, one of which being a category column with 50 different possible values. A formula counts up how many times each of these values occur. From there, I have a Rank formula beside each one; for example =RANK(S3,$S$3:$S$53).

Here's where the problem is coming in...

On the main page of my book I have a section to show the top ten categories. What I have it doing is a VLOOKUP of values 1 through 10 against the results of the RANK formula. The problem coming in is when there is a tie. Take the below for example:

Category One 192
Category Two 139
Category Three 139
Category Four 98
Category Five 212

So, with the above example the RANK formula would give both Category Two and Three a rank of 3 then skip rank 4 and give Category Four a rank of 5. So, when the VLOOKUP goes looking for the 4 it doesn't find it and errors out.

What I am looking for a formula that will take those 50 values and rank the top ten without duplicates.

Hope you can help!!

You mean something like:

=RANK(A1,$A$1:$A$10)+COUNTIF($A$1:A1,A1)-1

copied down

Adjust ranges to suit.

This should give unique ranks and not repeat rank numbers.

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

=RANK(A1,$A$1:$A$10)+COUNTIF($A$1:A1,A1)-1 in b1
not
=RANK(A1,$A$1:$A$10)+COUNTIF($A$1:A10,A1)-1

Thanks Martin,

I must have copied and pasted the last formula in my test range instead of the first that should have been copied down..... I guess the OP figured it out.

1.your formula is starting in the wrong place
it should be
=RANK(C2,$C$2:$C$4098)+COUNTIF($C$2:C2,C2)-1
but you are right it seems that countif () on rows 216 and 224 6.55738E-06
counts those as the same but rank() sees them as different
it is probably the way the number is stored from your original data. and excels precision

melknothink, for future reference, please do not post a question in the thread of another member -- start your own thread

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.