Macro to to create Sequential Entries

[Xirrus1983]

I have column full of different Customer Names.

I need to create corresponding column that contains a code for the customer of the first three digits of their name and a two digit sequential number.

Example:

Advanced Concepts - ADV01​

Also if there is a repeat of the first three digits then it needs to use the next sequential number.

Example

Advanced Concepts - ADV01​

Advisory Board - ADV02​

The Customer name is in column A and the code is in B

Thank you for your help

 

[John_w]

Try this:

Public Sub Create_Customer_Codes()
    
    Dim dicCodes As Object
    Dim lastRow As Long
    Dim cell As Range
    Dim cust As String
    
    Set dicCodes = CreateObject("Scripting.Dictionary")
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).row

    For Each cell In Range("A1:A" & lastRow)
        cust = UCase(Left(cell.Value, 3))
        If Not dicCodes.Exists(cust) Then
            dicCodes.Add cust, 1
        Else
            dicCodes.Item(cust) = dicCodes.Item(cust) + 1
        End If
        cell.Offset(, 1).Value = cust & Format(dicCodes.Item(cust), "00")
    Next
                
End Sub

 

[HalfAce]

Nice.