Remove duplicates using Index & Large formula combination

Question

Given a data set that contains location & scores (see below), I am trying to create a list that shows the ranking of locations from largest to smallest and pulls in the corresponding location name and score.

Dallas 3.5

Boston 4.1

NY 4.0

Chicago 4.1

Orlando 3.5

I input the placement in column B (ex. 1,2,3,4,5) and used the following formula to pull in the location that corresponded to the rank (ex. 1st place = location w/ highest score, 5th place = location w/ lowest score):

=index(B4:b10,match(large(AA4:AA10,B3),AA4:AA10,0)

I then used this formula to pull in the corresponding score: = index(AA4:AA10,match(b2,B4:B10,0)

When I copy the formula down, Boston is repeated twice for the largest -- what do I need to do to get it to show Boston & Chicago (i.e. ignore a duplicate and go to the next location for the same score)?

Answer 1

When I run into the possibility of duplicate numbers for the RANK() function, I add or subtract a very small unique number in a helper column and base the RANK() function on those numbers instead.

       !\[\](http://xfprpw.blu.livefilestore.com/y1ptaw3vJqmYKmZQ7HW0uFtIZaE-UHKL7MbelUFMuR4HIS4rCrxz9_J5Sm45hKkBK1KDuuOXVhJbgUdpCQbdpZDR9ZkMaqF6D0Z/Rank_Duplicates.PNG?psid=1)

In this example, I used column D as a helper column with the formula,

=$B2-(ROW()*10^-4)

... which equates to subtracting the row number * 0.0001 from the rating value in column B. You may want to play around with the actual math, but I hope that gives you the idea behind it. Fill B2 down as necessary.