VBA: How to Condense Two Columns into a List

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