PDA

View Full Version : Adding a,b,c letter to duplicates



Northender
11-12-2009, 06:27 AM
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
11-12-2009, 06:47 AM
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

Northender
11-12-2009, 07:04 AM
Thank you for the quick responce, works perfectly. I would never have figured that out.

North

Bob Phillips
11-12-2009, 07:24 AM
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
11-12-2009, 12:40 PM
26 duplicates is the maximum though.