Unique Rank with Duplicate Entries
 

Hi,

I have a question about a slightly complicated Rank function I want to
write, and Im having a problem with it. Say you have the following data:

Bob
Jim
Bob
Dan
Bill
Jim
Bob
Matt
Bob
Jim
Dan
Matt
Greg

I want to make a list that ranks these entries in order of how often they
appear, and that takes into account ties. Like so:

Bob (4)
Jim (3)
Matt (2)
Dan (2)
Greg (1)
Bill (1)

Does anyone have any ideas? Ive come close using CountIf and Unique Ranks,
but I cant figure out how to resolve the problem of having the same names
occur more than once. I also want to do this with as few helper columns as
possible.

Thanks,

Unique Rank with Duplicate Entries
 

In B1 enter the array formula:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"") and copy down
In C1 enter the normal formula:
=IF(B1="","",COUNTIF(A:A,B1)) and copy down


You should see:

Bob Bob 4
Jim Jim 3
Bob
Dan Dan 2
Bill Bill 1
Jim
Bob
Matt Matt 2
Bob
Jim
Dan
Matt
Greg Greg 1

just ignore the blank rows.
--
Gary''s Student - gsnu201001


"Demosthenes" wrote:

Hi,

I have a question about a slightly complicated Rank function I want to
write, and Im having a problem with it. Say you have the following data:

Bob
Jim
Bob
Dan
Bill
Jim
Bob
Matt
Bob
Jim
Dan
Matt
Greg

I want to make a list that ranks these entries in order of how often they
appear, and that takes into account ties. Like so:

Bob (4)
Jim (3)
Matt (2)
Dan (2)
Greg (1)
Bill (1)

Does anyone have any ideas? Ive come close using CountIf and Unique Ranks,
but I cant figure out how to resolve the problem of having the same names
occur more than once. I also want to do this with as few helper columns as
possible.

Thanks,