# Sequential numbering of duplicates

Options

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!

• ✭✭✭✭✭✭
Options

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)

=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.

• ✭✭✭✭✭✭
Options

What is your existing RANKEQ formula?

• Options

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

• ✭✭✭✭✭✭
Options

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)

=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.

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!