Calculate ordinal number of replicates

[arvnranger]

I have a list of alphanumeric codes. Ideally these should be unique but I find there are duplicates, sometimes 3 of each value. I can use COUNTIF to find the number of instances per code. Is there a function or combination of functions I can use to calculate whether each value is the first, second or third instance of that code in the list, eg

A123,1
B456,1
B987,1
A123,2
C789,1
C345,1
A123,3
B987,2

Cheers,
Ivan.

 

[Tetra201]

Is this what you need?

=COUNTIF(A$1:A1,A1)

 

[Rick Rothstein]

Is this what you need?

=COUNTIF(A$1:A1,A1)

Or, if your title meant to suggest you actually wanted the ordinal suffix attached to the number...

=COUNTIF(A$1:A1,A1)&MID("thstndrdth",MIN(9,2*RIGHT(COUNTIF(A$1:A1,A1))*(MOD(COUNTIF(A$1:A1,A1)-11,100)>2)+1),2)

 

[arvnranger]

Or, if your title meant to suggest you actually wanted the ordinal suffix attached to the number...

=COUNTIF(A$1:A1,A1)&MID("thstndrdth",MIN(9,2*RIGHT(COUNTIF(A$1:A1,A1))*(MOD(COUNTIF(A$1:A1,A1)-11,100)>2)+1),2)

Excellent! Thanks, Tetra201 and Rick. Just had to make one end of the range relative to get a running count... so simple I feel a bit of a ninny.

Cheers,
Ivan.