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

Question

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.

hansvogelaar · Answer

rbarden&nbsp;
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 &lt;&gt; ""
        Do While Cells(CurRow + 1, NameCol).Value = "" And Cells(CurRow + 1, NameCol + 1).Value &lt;&gt; ""
            Cells(CurRow, NameCol + 1).Value = Cells(CurRow, NameCol + 1).Value &amp; ", " &amp; 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

rbarden · Answer

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

Forum Discussion

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

2 Replies

Resources