Excel formula to Rank list with duplicates

The problem with the RANK formula in Excel is that if it finds 2 numbers with the same rank, it gives them the same number. This is OK if you are just looking at the results. But if you need to use them elsewhere, and for example, you use a VLOOKUP to find all the items, only the first item will be found. So what is the Excel formula to Rank list with duplicates.

Unfortunately there isn’t one (at the moment) so you need to adjust your data slightly.

Below a YouTube video clip explaining the process with text and screen shots further down

Below you can see the problem. As a result of 2 sales people having the same total sales they are both ranked 4th. That means if I try and create a formula based on sorting using VLOOKUP, the 5th place is missing.

In order to make sure this doesn’t happen, we need to build helper cells which will make sure that a tie is not possible. One way to do this is to add a very small number to the decision cells.

In our example the Total Sales is the ranking column. If there are some rules that determine who should be 4th and who should be 5th these need to be applied. So in sports events e.g. World Cup Soccer, if two teams are on the same points it then needs to look at goals scored, then goals against etc.

In this case we just want them to be different. As shown below we have added a Unique Identifier column and entered very small numbers (down to the 6th decimal). These have been added to the Total Sales to give the Unique Sales Rank. Note that the very small numbers make each cell unique and the small number does not affect the total sales. If you added numbers like 0.1 you could affect the total sales.

Once you have this new column, you can perform the rank on this and it will always be unique.

For the instance mentioned above where you may have some rule saying who should be ranked higher you can divide that decision number by a big number. So if we decided that the person selling more units should be higher, then create a column that is equal to units sold divided by say 10 000. This will generate a small number that can be added to the original.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Remove duplicates NOT WORKING if number formats different

Go to the next duplicate in Excel