Unique Rank with Duplicate Entries

D

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

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$2:D2,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$2:D2)>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))
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top