Convert Second Column Data to Multiple Columns while retaining First Column as Reference

rbarden 

Here is a macro you can run:

Sub CombineColors()
    Const NameCol = 3
    Const FirstRow = 4
    Dim CurRow As Long
    Application.ScreenUpdating = False
    CurRow = FirstRow
    Do While Cells(CurRow, NameCol + 1).Value <> ""
        Do While Cells(CurRow + 1, NameCol).Value = "" And Cells(CurRow + 1, NameCol + 1).Value <> ""
            Cells(CurRow, NameCol + 1).Value = Cells(CurRow, NameCol + 1).Value & ", " & Cells(CurRow + 1, NameCol + 1).Value
            Cells(CurRow + 1, NameCol).Resize(1, 2).Delete Shift:=xlShiftUp
        Loop
        CurRow = CurRow + 1
    Loop
    Cells(1, NameCol + 1).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub