CODEWITHSUNDEEP
excelduplicatesrank
Andrew Abbott
Andrew Abbott

Reputation: 437

Excel function for ranking duplicate values

I have an excel sheet containing two columns of data that I'd like to rank.

Suppose we have the following:

    A        B
    Franz    58
    Daniel   92
    Markus   37
    Jörg     58

I would like a formula to rank the above data based on column B, and where there are duplicate values (Franz and Jörg) to put the alphabetical name first. What I have at the moment is simply duplicating Franz twice:

     =INDEX(Name,MATCH(A2,Points,0))

Can someone advise me of formula / code that will rank the data and arrange duplicate values alphabetically?

Thanks

Upvotes: 1

Views: 19836

Answers (3)

tom bannister
tom bannister

Reputation: 131

This will rank your data and will not repeat duplicates too:

In C2:

=SUM(1*(b2>$b$2:$b$5))+1+IF(ROW(b2)-ROW($b$2)=0,0,SUM(1*(b2=OFFSET($b$2,0,0,INDEX(ROW(b2)-ROW($b$2)+1,1)-1,1))))

CTRL+SHIFT+ENTER to turn it into an array

Drag these down to C5 and it will not duplicate rank where the name is the same, it will rank them alphabetically if they are the same.

Then if you wanted to order them automatically in order of top performer/score you then do this:

Putting this in E2:

=INDEX(A2:A5,MATCH(LARGE(C2:C5,ROW()-1),C2:C5,0))

...and drag down

Then use a vlookup on your data to return the score putting this in F2:

=vlookup(E2,A2:C5,2,false)

...and drag down

This should give you a table of highest scoring people in score order.

Upvotes: 2

Rick
Rick

Reputation: 1073

I would add a helper column in next to your data to help out with ties. so in column C use

=B1+1/COUNTIF($A$1:$A$4,"<="&A1)/10

This will add on a decimal ranking system based on the name. This assumes that your numbers in column B do not have decimal places, if they do then you will need to increase the 10 on the end of the formula to account for it ie: for 2 decimal places use 1000, 3 : 10000 etc

Use this formula to get the first name

=INDEX(name,MATCH(LARGE(points,1),points,0))

adjust the 1 to 2 for the second name etc

EDIT had the sign around the wrong way

Upvotes: 4

barry houdini
barry houdini

Reputation: 46341

Assuming A2 is the first of the ranked points scores try this version

=INDEX(Name,SMALL(IF(A2=Points,ROW(Points)-MIN(ROW(Points))+1),COUNTIF(A$2:A2,A2)))

confirmed with CTRL+SHIFT+ENTER and copied down

Requires the Name list to be sorted because names with duplicate scores will be listed in the order shown

Upvotes: 1

Related Questions