Thread: Concatenating Names based on adjacent duplicate values

This is a small example of the project I'm slamming my head into:


99988 John
99989 John
99990 John
99991 Chad
99991 Alice
99992 Chad
99993 Chad
99994 Chad
99995 Chad
99996 Chad
99997 Chad
99998 Chad
99998 Alice
99999 Chad

What I'm trying to do is concatenate the names by number, either removing duplicates in the process, or populating an already existing list that doesn't have duplicates. So, ideally the end result would look like this:

99988 John 99988 John
99989 John 99989 John
99990 John 99990 John
99991 Chad 99991 Chad, Alice
99991 Alice 99992 Chad
99992 Chad 99993 Chad
99993 Chad 99994 Chad
99994 Chad 99995 Chad
99995 Chad 99996 Chad
99996 Chad 99997 Chad
99997 Chad 99998 Chad, Alice
99998 Chad 99999 Chad
99998 Alice
99999 Chad

Any help at all would be appreciated in helping figure this out. I've quite honestly run out of ideas.

Thank you very much!

Try this...

Sub NN_List()
    Dim x As Long

    With Range("Num_List")  ''CREATE NAMED RANGE TO MATCH YOUR LIST (NUMBERS ONLY)
        For x = .Count To 1 Step -1
            If .Item(x) = .Item(x - 1) Then
                .Item(x - 1).Offset(0, 1) = .Item(x - 1).Offset(0, 1).Value2 & ", " & .Item(x).Offset(0, 1).Value2
                Range(.Item(x), .Item(x).Offset(0, 1)).Delete Shift:=xlUp
            End If
        Next x
    End With
End Sub

This should be faster...

Sub NN_List()    Dim vData As Variant
    Dim x As Long


    vData = Worksheets("Sheet1").Range("A2:B" & Range("B2").End(xlDown).Row)
    For x = UBound(vData) To 2 Step -1
        If vData(x, 1) = vData(x - 1, 1) Then
            vData(x - 1, 2) = vData(x - 1, 2) & ", " & vData(x, 2)
            vData(x, 1) = ""
            vData(x, 2) = ""
        End If
    Next x
    Worksheets("Sheet1").Range("A2:B" & Range("B2").End(xlDown).Row) = vData
End Sub