Accounting for Duplicates in Sequential Descending Lists

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have large set of data with names down the far left
side (Column A) and the number of times those names
appear in columns (B:AU).
I would like to form a table with the top 10 names
appearing in the list in descending order.
IE:
Smith (10x)
Jones (10x)
White (9x)
etc.
I can do this no problem with the following functions:
=INDEX($A:$A,MATCH(LARGE(B$57:B$106,1),B:B,0)
=INDEX($A:$A,MATCH(LARGE(B$57:B$106,2),B:B,0)
and so on down to the 10th largest.

The problem is that if two (or more) names appear the
equal number of times, the first name is simply repeated
(in example above it would read: Smith, Smith, White)

How can I have each name used only once in the table even
if there are duplicates? ie, not duplicate Smith

Thank you so much for the help.
David
 
Hi one way:
change your formula to the array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$200,MATCH(LARGE($B$1:$B$200+(ROW($B$1:$B$200))/1000,ROW(
)),$B$1:$B$200+(ROW($B$1:$B$200))/1000,0))

and copy down. If this list does not start in the first row you have to
change the second parameter of the LARGE function (ROW()) accordingly
 
Back
Top