=RANK applying nth instance of duplicate to next n; avoid sharing

**AlvaroSiza** · 02-25-2012, 08:43 PM

Data is vertically aligned, containing dates (formatted as mm/dd/yyyy) within the range BG68:BG213. Rank values are returned in each row, offset to Column BH.

In this example, assume that the nearest date to today is shared by two instances; thus the =RANK returns a 1 for both, skips 2 and applies a 3 to the next newest date.

I would like to have the 2nd instance of the 1 be a 2 and am happy to disregard the logic behind which of the two data points get the 1 and which gets the 2. Whichever occurs first in the vertical range gets the 1.

I believe the following could be of assistance, I'm just struggling with the application.

Link: http://www.excelforum.com/excel-work...s-in-rank.html

Thanks!

**JBeaucaire** · 02-25-2012, 09:06 PM

That link provides the solution for you in post #2. Just edit the ranges. You can do it. Pay attention to get the $ signs in the right places.

**FDibbins** · 02-25-2012, 09:21 PM

didnt read properly, sorry

**AlvaroSiza** · 02-25-2012, 10:31 PM

J, when applying the method via the link:

1. The revised ranks contain duplicates; &
2. Are in reverse order (oldest is ranked 1st, which should be switched).

Please see attached dummy wb. Thanks!

EDIT: Was attempting to apply the fix proposed in the final post, not Post #2. Changed order to 0. All is good.

Thanks,