Splitting multiple columns data into rows

Question

I have about 1000 unique rows with columns to be split. I am new to VBA and not sure how to split multiple columns into rows with holding ID. The number of elements in a column can varies between rows but is the same across columns in the same row .

Input:

+----+---------------+----------+----------+
| id |     col1      |   col2   |   col3   |
+----+---------------+----------+----------+
|  1 | abc, bcd, cde | aa,bb,cc | 1a,2b,3a |
|  2 | abc, ded      | ba,de    | a7,7a    |
|  3 | a,b,c,d       | d,c,d,a  | f,d,s,a  |
+----+---------------+----------+----------+

Output:

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | abc  | aa   | 1a   |
|  1 | bcd  | bb   | 2b   |
|  1 | cde  | cc   | 3a   |
|  2 | abc  | ba   | a7   |
|  2 | ded  | de   | 7a   |
|  3 | a    | d    | f    |
|  3 | b    | c    | d    |
|  3 | c    | d    | s    |
|  3 | d    | a    | a    |
+----+------+------+------+

Your help will be greatly appreciated!

I don't know why "VBA: Split cell values into multiple rows and keep other data" this link as a previous answer but I think splitting one column and multiple columns are different questions.

@Pᴇʜ I don't know why "VBA: Split cell values into multiple rows and keep other data" this link as a previous answer but I think splitting one column and multiple columns are different questions. — no_name, Jul 27 '18 at 07:36
You might misunderstood this website. This is not a free coding service, and usually people here don't write the code for you if you did nothing at all. The link I gave you shows how to split one column and can easily extended into multiple columns. Of course you must put in some effort yourself too. — Pᴇʜ, Jul 27 '18 at 07:38
@Pᴇʜ I disagree with you that I don't spend any single time. For the novice like me, someone also need to even learn how to write ASCII table in stackoverflow. That was my self-learning point today. — no_name, Jul 27 '18 at 07:43

score 1 · Accepted Answer · answered Jul 27 '18 at 07:18

Try this code (comments in code):

Sub Expand()
    Dim currentRow As Long, lastRow As Long, table As Variant, i As Long, _
        valuesInOneRowCol1 As Variant, valuesInOneRowCol2 As Variant, valuesInOneRowCol3 As Variant
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    currentRow = 2
    'read hwole range to memory and clear the range to fill it with expanded data
    table = Range("A2:D" & lastRow).Value2
    Range("A2:D" & lastRow).Clear
    For i = 1 To lastRow - 1
        'split comma separated lists in each column
        valuesInOneRowCol1 = Split(table(i, 2), ",")
        valuesInOneRowCol2 = Split(table(i, 3), ",")
        valuesInOneRowCol3 = Split(table(i, 4), ",")
        'write all data from one row
        For j = LBound(valuesInOneRowCol1) To UBound(valuesInOneRowCol1)
            Cells(currentRow, 1) = table(i, 1)
            Cells(currentRow, 2) = valuesInOneRowCol1(j)
            Cells(currentRow, 3) = valuesInOneRowCol2(j)
            Cells(currentRow, 4) = valuesInOneRowCol3(j)
            currentRow = currentRow + 1
        Next
    Next
End Sub

Splitting multiple columns data into rows

1 Answers1

Linked