Sequential numbering of duplicates
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!
Best Answer
-
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.
Answers
-
What is your existing RANKEQ formula?
-
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
-
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.
-
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
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!