Nov 18 2021 10:57 AM - edited Nov 18 2021 11:00 AM
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.
Nov 18 2021 11:58 AM
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
Nov 18 2021 02:50 PM
Thanks for this! It works great without the intermediate steps. I need to learn more about Macros, but your assistance was immensely helpful!