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.

Paul Newcome

What is your existing RANKEQ formula?

MJMO

Hi Paul,

I'm using =RANKEQ([Total Order Qty]@row, [Total Order Qty]:[Total Order Qty])

This is used as a column formula, shown in the screen shot below. You can see a couple of examples of the duplication/skipping where multiple orders have the same qty.

This is a driving a metric that shows the top 5 orders by quantity and value, so I would want those duplicate orders listed independently.

Thanks!

MJ

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.

MJMO

Thanks Paul!

I have some more to do with the data from there, but this solution got me a ranking dataset that I can use, plus I can use the two counting columns for ranking several other elements the same way. Appreciate the help!

MJ

Paul Newcome

Happy to help. 👍️