VBA: How to Condense Two Columns into a List

VBA Column Condenser

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.

A GifGuide Gif

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
Advertisements
Tagged , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: