0

In Excel, I have multiple columns with delimited data that need to be split into new rows. Some columns have single values that would need to be copied to the new rows. I can only see examples on the web of a single column with delimited data, so not sure how to apply solutions to multiple columns containing delimited data.

I need to create individual rows per delimited item where multiple columns contain delimited lists

What I have:

enter image description here

What I need:

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
lebowski22
  • 3
  • 1

1 Answers1

0

Here is a simple example on how you could approach this:

Sub Test()

Dim MyArray1() As String, MyArray2() As String
Dim X As Long, Y As Long, Z As Long

Z = 7
For X = 2 To 4
    MyArray1() = Split(Replace(Cells(X, 2).Value, " ", ""), ",")
    MyArray2() = Split(Replace(Cells(X, 3).Value, " ", ""), ",")
    For Y = LBound(MyArray1) To UBound(MyArray1)
        Cells(Z, 1).Value = Cells(X, 1).Value
        Cells(Z, 2).Value = MyArray1(Y)
        Cells(Z, 3).Value = MyArray2(Y)
        Cells(Z, 4).Value = Cells(X, 4).Value
        Z = Z + 1
    Next Y
Next X

End Sub

Output below

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks. Is there a way to insert rows into the current table, as opposed to creating a new table below. The reason is, it could exceed the row limit per sheet. If not, can the output table be added to a new sheet? – lebowski22 Feb 07 '19 at 13:49
  • Ofcourse it can! Just play around with where you want your output to be. Adding sheet references for example will be a quick solution to get your data on another sheet. – JvdV Feb 07 '19 at 13:55
  • Works perfectly, thanks again and for such a swift solution. – lebowski22 Feb 08 '19 at 10:01
  • If a cell in one of the delimited fields is empty, the row is skipped in the output. Can you recommend code to cater for empty arrays? – lebowski22 Feb 08 '19 at 10:25