SOLVED

# Need help ordering duplicated values in table

Copper Contributor

# 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
4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Need help ordering duplicated values in table

Perhaps like this:

``=COUNTIF(\$A\$2:A2,A2)``

# Re: Need help ordering duplicated values in table

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)
)``````

# Re: Need help ordering duplicated values in table

@OliverScheurich this worked!! Thank you!

# Re: Need help ordering duplicated values in table

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
)
)``````
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Need help ordering duplicated values in table

Perhaps like this:

``=COUNTIF(\$A\$2:A2,A2)``