Unique Rank with Duplicate Entries

[Demosthenes]

Hi,

I have a question about a slightly complicated Rank function I want to
write, and I’m 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? I’ve come close using CountIf and Unique Ranks,
but I can’t 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,

 

[T. Valko]

Try one of these...

Data in the range A2:A14 and there are no empty cells within the range.

If you *don't* want to use a helper column...

Enter this array formula** in D2:

=INDEX(A2:A14,MODE(MATCH(A2:A14,A2:A14,0)+{0,0}))

Enter this array formula** in D3 and copy down until you get blanks:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A$2:A$14,MODE(IF(COUNTIF(D$22,A$2:A$14)=0,MATCH(A$2:A$14,A$2:A$14,0)+{0,0})))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If you don't mind using a helper column...

Enter this formula in B2 and copy down to B14:

=IF(COUNTIF(A$2:A2,A2)>1,"",COUNTIF(A$2:A$14,A2)-ROW()/10^10)

Enter this formula in D2 and copy down until you get blanks:

=IF(ROWS(D$22)>COUNT(B$2:B$14),"",INDEX(A$2:A$14,MATCH(LARGE(B$2:B$14,ROWS(C$2:C2)),B$2:B$14,0)))

Enter this formula E2 and copy down until you get blanks:

=IF(C2="","",COUNTIF(A2:A14,C2))

 

[T. Valko]

Ooops!

Enter this formula E2 and copy down until you get blanks:
=IF(C2="","",COUNTIF(A2:A14,C2))

Should be:

=IF(D2="","",COUNTIF(A$2:A$14,D2))

 

[Bernd P]

Hello,

I suggest to look at:
http://sulprobil.com/html/sorting.html
http://sulprobil.com/html/rank.html

Regards,
Bernd

 

[Ashish Mathur]

Hi,

Create a pivot table - drag names to the row area and data area. Then just
sort the data area numbers in descending order

 

[Demosthenes]

Excellent! Both work just like I want. Thanks for your help!

 

[T. Valko]

You're welcome. Thanks for the feedback!