A visitor wrote
See Updated Post on Ranking Excel Lists with Duplicates
Hi. I was watching your video on ‘Automatic Sorting of Lists’ using the RANK and VLOOKUP functions, and I’m trying to make a spreadsheet tallying up an amount of votes earned by football players per game in an MVP. I’ve got a total votes for each player that I have used to make a ‘rank’ alongside their name, but some of these ranks of the same (eg. there are two 1sts, then it jumps straight to 3rd).
When I try to use VLOOKUP to create a Top Ten list of the top players, I number 1-10 in one column, and use the VLOOKUP formula as explained in your video. It shows the first 1st player in the list, but not the second, as was also explained in your video as a potential problem. Are there any ways to overcome this?
Unfortunately you need to make sure that the ranks can never be the same. In your example above I presume that there are no other criteria that would factor into who would be ranked top. Therefore what I would do is give each player a unique number e.g.
1- Adrian- 20 votes
2- Willem- 20 votes
3- Ben- 19 votes
For the column you use to rank, add a decimal of the unique player number e.g. I divide the player number by say 1000 so Adrian’s votes would be 20 + (1/1000) = 20.001, Willem would be 20 + (2/1000)= 20.002 and Ben would be 19 + (1/1000)= 19.001 .
Now every player will have an absolutely unique ranking number even though their votes may be the same (perhaps create 2 columns- one for the actual votes received and one for the column to use to rank it). The VLOOKUP will look for only that exact number so there never can be a duplicate on the ranking criteria.
Below a YouTube video example of something similar.
On our site we highlight how to use the RANK function ( https://www.auditexcel.co.za/Rank%20Project.html ) and how to do automatic ranking ( https://www.auditexcel.co.za/Automatic%20Sorting%20Project.html ).
Learn: Using VLOOKUP in multiple ways