Adding a,b,c letter to duplicates

[Northender]

I have been searching for how to add a letter sequence to found duplicates, but must be using the wrong words.

I have a file and am creating a unique reference for each line based on todays data and data in one of the fields. however there will be some duplicates in this that need to be lettered A, B, C etc. once that reference is done, return to A for the next duplicate.

Example :

121109-39 needs to be 121109-39
121109-42 needs to be 121109-42A
121109-42 needs to be 121109-42B
121109-07 needs to be 121109-07
121109-10 needs to be 121109-10A
121109-10 needs to be 121109-10B
121109-10 needs to be 121109-10C

Think you get the picture.

I know how to create the references etc, just not the but to work out the duplicates and add the letter, and then reset the letter ready for the next duplicate.

Thanks
North

[mdmackillop]

[VBA]
Sub Suffix()
Dim rng As Range, Cel As Range
Dim Cnt As Long, cntAll As Long
Set rng = Selection
For Each Cel In rng
cntAll = Application.CountIf(rng, Cel)
If cntAll > 1 Then
Cnt = Application.CountIf(Range(rng(1), Cel), Cel)
Cel.Offset(, 1) = Cel & Chr(64 + Cnt)
Else
Cel.Offset(, 1) = Cel
End If
Next
End Sub
[/VBA]

[Northender]

Thank you for the quick responce, works perfectly. I would never have figured that out.

North

[Bob Phillips]

I posted this formula earlier, don't know what happened to it

=IF(COUNTIF($A:$A,$A1)>1,$A1&CHAR(64+COUNTIF($A$1:$A1,$A1)),$A1)

[jolivanes]

26 duplicates is the maximum though.