How can I loop through a list in Excel VBA and concatenate unique and duplicate IDs from two columns?

Question

I am trying to loop through a list of IDs in column A and return the (last or 5th character) of the ID string for unique/distinct ID’s in column B.

Next, I want to look at duplicated IDs (based on the first 4 characters) of the ID string and return the last (or 5th character) of the ID string in the cell adjacent to the first duplicate (please see below picture). The idea is to concatenate the parsed (last or 5th character) from each of the duplicate values and return them in the cell adjacent in column B (please see below picture).

I tried using the VBA example found here, but could not get it to work for my problem.

Answer 1

Another way, only if the ID value is always 5 character ....

Sub test()
Dim rg As Range, cell As Range, c As Range, d As Range
Dim arr, fa As String

With Sheets("Sheet1")
Set rg = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

Set arr = CreateObject("scripting.dictionary")
For Each cell In rg: arr.Item(Left(cell.Value, 4)) = 1: Next

    For Each el In arr
    Set c = Columns(1).Find(el, lookat:=xlPart, after:=Cells(1, 1))
    fa = c.Address
    Set d = c.Offset(0, 1): d.Value = Right(c.Value, 1)
        Do
            Set c = Columns(1).Find(el, lookat:=xlPart, after:=c)
            If d.Find(Right(c.Value, 1), lookat:=xlPart) Is Nothing _
            Then d.Value = d.Value & ", " & Right(c.Value, 1)
        Loop Until c.Address = fa
    Next

End Sub

The sub create range variable from A2 to whatever lastrow with data as rg.
Then it loop to each cell in rg to create a unique value which is the first four character from the left of the looped cell value as arr variable.

Then it loop to each element in arr.
Get the first found cell in column A which value is the looped element as c variable, then set a range of c.offset(0,1) as d variable and fill the d with the last character of c value.

Then it loop to find the next element in column A, and check if the next found cell last character value not exist in d, it add this last character to d.

--->