Forum Discussion
Solved
Need help ordering duplicated values in table
Hello! I have a table with thousands of duplicate IDs and I am trying to create an sequential order to show how many times the IDs are listed. The CountIF function gives me the total count listed on each row and that is not what I am looking for. Any help is much appreciated. Thank you!
| MBR_CONTR_EXT_ID | CountIf | What I want |
| 800117684-00 | 1 | 1 |
| 800117800-00 | 2 | 1 |
| 800117800-00 | 2 | 2 |
| 800117805-00 | 2 | 1 |
| 800117818-00 | 2 | 1 |
| 800117921-00 | 1 | 1 |
| 800117979-00 | 1 | 1 |
| 800118080-00 | 2 | 1 |
| 800118080-00 | 2 | 2 |
| 800118207-00 | 1 | 1 |
| 800118291-00 | 1 | 1 |
| 800118294-00 | 1 | 1 |
| 800118308-00 | 4 | 1 |
| 800118308-00 | 4 | 2 |
| 800118528-00 | 1 | 1 |
| 800118627-00 | 1 | 1 |
| 800118730-00 | 2 | 1 |
| 800118746-00 | 3 | 1 |
| 800118747-00 | 5 | 1 |
| 800118747-00 | 5 | 2 |
| 800118747-00 | 5 | 3 |
| 800118797-00 | 5 | 1 |
| 800118797-00 | 5 | 2 |
| 800118797-00 | 5 | 3 |
| 800118797-00 | 5 | 4 |
| 800118797-00 | 5 | 5 |
A 365 solution with MAP:
MBR_CONTR_EXT_ID is defined as a dynamic item
=LET( seq, SEQUENCE(ROWS(MBR_CONTR_EXT_ID)), CountID, LAMBDA(s, ID, COUNTIF(TAKE(MBR_CONTR_EXT_ID, s), ID)), MAP(seq, MBR_CONTR_EXT_ID, CountID) )As variant
=LET( rng, MBR_CONTR_EXT_ID, n, ROWS(rng), SumIt, LAMBDA(v, SUM(v) ), BYROW( (TRANSPOSE(rng)=rng )* (SEQUENCE(n) >= SEQUENCE(,n) ), SumIt ) )
OliverScheurich this worked!! Thank you!
