You know what I’ve wanted all my life? A way to condense columns into comma separated lists. I’ve been in multiple jobs where this would’ve been handy because it’d be so much easier to see all the things in a few rows.
So here it is. VBA to the rescue, once again, coded into a button. A couple of disclaimers—the column to be collapsed needs to be in Column A for this to work. The code below will put those values into Column D. And the associated values in Column B will be pushed into column E.
The Code:
Private Sub CommandButton1_Click() Dim i As Long Dim x As Long Dim A As String Dim B As String x = 1 A = "Whatever" For i = 1 To UsedRange.Rows.Count 'Loop through each value in Column A If A <> Cells(i, 1).Value Then 'If this is a new value A = Cells(i, 1).Value 'Make that value equal to variable A B = Cells(i, 2).Value Cells(x, 4).Value = A 'Add a cell in Column D with the A variable Cells(x, 5).Value = B 'Add the associated B value to Column E x = x + 1 'Move x over one to move down a column Else If x = 1 Then 'Concatenate strings with & Cells(x, 5).Value = Cells(x, 5).Value & ", " & Cells(i, 2).Value Else Cells(x - 1, 5).Value = Cells(x - 1, 5).Value & ", " & Cells(i, 2).Value End If End If Next i MsgBox "Done" End Sub