Skip to content

Instantly share code, notes, and snippets.

@torbiak
Created January 30, 2018 03:28
Show Gist options
  • Save torbiak/9752d08f0acec74fd5ef582a92753a52 to your computer and use it in GitHub Desktop.
Save torbiak/9752d08f0acec74fd5ef582a92753a52 to your computer and use it in GitHub Desktop.
Excel VBA subroutine to split a multi-value column into multiple boolean columns
' Split the selected column containing values like "a;b;c", "a;c" into boolean
' 0/1 columns for each of "a", "b", "c".
Sub SplitCol()
Dim c As Range
Dim newCols As Object
Dim ncols As Long
ncols = 0
Set newCols = CreateObject("Scripting.Dictionary")
For Each c In Intersect(ActiveSheet.UsedRange, Selection.EntireColumn).Offset(1)
Dim s As Variant
For Each s In Split(c.Value, ";")
s = Trim(s)
If s = "" Then GoTo NextSplit
If newCols.Exists(s) Then
Cells(c.Row, Selection.Column + newCols.Item(s)) = 1
Else
ncols = ncols + 1
ActiveSheet.Columns(Selection.Column + ncols).Insert
Cells(1, Selection.Column + ncols) = s
Call newCols.Add(s, ncols)
Cells(c.Row, Selection.Column + ncols) = 1
End If
NextSplit:
Next
Next
Intersect(ActiveSheet.UsedRange, _
Range(Columns(Selection.Column + 1), _
Columns(Selection.Column + ncols))) _
.SpecialCells(xlCellTypeBlanks) = 0
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment