Problem with Duplicates in Ranking

[DPChristman]

The short version: I am running into a problem with duplicates in Ranking.

Details:

I have a listing of about 160 clients listing the number of purchases each month from each.

These clients are ranked, using the formula = IFERROR (RANK (O3,O$3:O$161,0 )," " ) (spaces added to prevent formatting issues here). This Tab is called MONTHLY SUMMARY.

On another tab (RANKING) I have a list of numbers (1-25) in Column A

Using a vlookup formula = IFERROR ( VLOOKUP ( A2,'MONTHLY SUMMARY'!A:B,2,0 ) ,"NONE" ), I maintain a moving listing of what clients are ranked in the TOP 25 by purchases.

The problem is that due to non-unique numbers, I am missing rankings from 1-25. Example I have two clients with 112 for the YTD - They are both ranked at 14. As a result, there is no 15th ranked client.

This is over a 5 month period, and the odds are that by the end of the year there probably won't be a tie between companies in the top 25. However, that doesn't fix my problem now

I added the IFERROR,"NONE" piece to the vlookup to avoid the #N/A error, but I would prefer a clean ongoing listing of my top 25 clients. is there a way to do this?

 

[jorismoerings]

Hi,

Change your RANK formula to this: =IFERROR (RANK(O3,$O$3:$O$161)+COUNTIF($O$3:O3,O3)-1," " ).
Enter it in O3 and copy down to O161

This will take care of the duplicates in your RANK.
You can leave the VLOOKUP intact.

 

[DPChristman]

Perfect! Thanks!