Sequential numbering of duplicates

MJMO

Hi, I have a row of rankings and there are duplicate values in the data set. RANKEQ gives duplicates the same rank and then skips the number of duplicates before going to the next rank, (so for example, 1 2 3 3 5 6 7 7 7 10)

I need a formula to number the duplicates such that a unique value, or the first duplicate would return 0, the next duplicate would return 1, the next 2, etc. From there I can add my ranked column and my counter column and get a dense rank (no duplicates, no skips).

I have tried solutions from other posts using RANKEQ + COUNTIF, or multiple IFERROR columns but they haven't gotten the result I'm looking for. Thanks!

Paul Newcome

Ok. My suggestion would be to insert two helper column (that can be hidden after setting up).

Auto-number column ("Auto") with no special formatting.

Text/number column ("Row") with the following column formula:

=MATCH(Auto@row, Auto:Atuo, 0)

Then you can adjust your RANKQ like so:

=VALUE(RANKEQ([Total Order Qty]@row, [Total Order Qty]:[Total Order Qty]) + "." + COUNTIFS([Total Order Qty]:[Total Order Qty], @cell = [Total Order Qty]@row, Row:Row, @cell <= Row@row))

Basically we append the "." and then the count of how many rows before contained that particular number. Wrapping it in the VALUE function converts it back into a usable number.