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

New Contributor

I have a situation where I have two columns of data that I need to convert the second column into multiple columns while retaining the first column as the reference column.   The second column can have a variable number of text entries, depending on the first column (not a fixed number of entries in the second column).   The objective is to get the second column into one text cell.  I can do that using concatenate or textjoin, so that isn't an issue.  Any help would be appreciated!


Updated:  Rev 2.0 has the correct reference to avoid confusion. 

2 Replies


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
        CurRow = CurRow + 1
    Cells(1, NameCol + 1).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

@Hans Vogelaar 


Thanks for this!   It works great without the intermediate steps.   I need to learn more about Macros, but your assistance was immensely helpful!