I want to go from something like the first table to the second table:
…For the sake of using in a pivot table. I would like the first table to be on one sheet, and the second table to be another sheet, live-updating this “exploded” second table. I’ve been trying for a while and cannot get this to work. Any suggestions? The form I am using is outputting this list-type of comma separated values in single cells, and in this case it is not practical to do manually as there would be thousands of rows.
I modified the script from the link gtwebb provided. Here is the script:
Option Explicit Sub Main() Columns("B:B").NumberFormat = "@" Dim i As Long, c As Long, r As Range, v As Variant For i = 1 To Range("B" & Rows.Count).End(xlUp).Row v = Split(Range("B" & i), ", ") c = c + UBound(v) + 1 Next i For i = 2 To c Set r = Range("B" & i) Dim arr As Variant arr = Split(r, ", ") Dim j As Long r = arr(0) For j = 1 To UBound(arr) Rows(r.Row + j & ":" & r.Row + j).Insert Shift:=xlDown r.Offset(j, 0) = arr(j) r.Offset(j, -1) = r.Offset(0, -1) r.Offset(j, 1) = r.Offset(0, 1) Next j Next i Columns("C:C").NumberFormat = "@" Dim k As Long, d As Long, s As Range, w As Variant For k = 1 To Range("C" & Rows.Count).End(xlUp).Row w = Split(Range("C" & k), ", ") d = d + UBound(w) + 1 Next k For k = 2 To d Set s = Range("C" & k) Dim arrb As Variant arrb = Split(s, ", ") Dim m As Long s = arrb(0) For m = 1 To UBound(arrb) Rows(s.Row + m & ":" & s.Row + m).Insert Shift:=xlDown s.Offset(m, 0) = arrb(m) s.Offset(m, -1) = s.Offset(0, -1) s.Offset(m, -2) = s.Offset(0, -2) Next m Next k End Sub
Since I only needed this for two columns I didn’t bother with looping. The only things modified are that the script is repeated a second time, variables are changed, and the
Offset parameter is changed.