Find the row number of an ID in relation to the first time that ID appears

[cfoxc]

I have a file with a list of IDs that are repeated a number of times. The IDs will always be duplicated in a group, if at all. I want to add a column that gives the position of the ID in relation to the first time that it appears (essentially indexing duplicates). I have attached an example but I'm also writing it below.

If anyone knows anything that can do this for me any help would be greatly appreciated.



ID Position in list of itself
61 1
61 2
61 3
51 1
71 1
81 1
81 2
81 3
91 1
91 2
91 3
91 4

[Ace_XL]

In B2 use

Please Login or Register  to view this content.

Copy down

[FDibbins]

based on your example if your file, try this in C2 (i used C2, so i could compare with your answers), copied down...

=IF(A2=A1,C1+1,1)

[cfoxc]

Oh my god, thank you. It seems so obvious now - I think I was just staring at this for too long!