How to split a cell value into multiple rows while keeping other cell data?

Question

I have a range of cells like the below:

Col A         | Col B      | Col C | Col D
apple, orange | red, green | 5     | 10
pear, banana  | blue       | 20    | 40

I want to get a result that looks like:

Col A 
apple-red-5-10
apple-green-5-10
orange-red-5-10
orange-green-5-10
pear-blue-20-40
banana-blue-20-40

I am concatenating values in each row across multiple columns, but if a cell contains a comma separated list of an item, I need to treat each entry as if they belong in a new row with other column data included.

I first reference the range belonging to the four columns I am interested in concatenating.

Dim cellRef1 As Range
Set cellRef1 = ThisWorkbook.Worksheets(1).Range("I2:I60")
Dim cellRef2 As Range
Set cellRef2 = ThisWorkbook.Worksheets(1).Range("E2:E60")
Dim cellRef3 As Range
Set cellRef3 = ThisWorkbook.Worksheets(1).Range("D2:D60")
Dim cellRef4 As Range
Set cellRef4 = ThisWorkbook.Worksheets(1).Range("H2:H60")

Then for the columns which would have comma separated entries (second and fourth), I create an array to store each value e.g.:

Dim Array_Two As Variant
Array_Two = cellRef2.Value

So I would have an array along the lines of

 ("apple, orange", "pear,banana")

I wanted to loop through each array to create a sub-array where I want to split the entries by a comma and store them in a new array.

e.g. I want:

Array_Two_A = ("apple", "orange")
Array_Two_B = ("pear", "banana)

So I can later access each entry and concatenate them.

I wanted to do this using:

Dim Array_Two_New() As String

For x = LBound(Array_Two) To UBound(Array_Two)
    Array_Two_New = Split(Array_Two(x, 1), ",")
Next x

Which kind of works, but the problem is I can't figure out how to dynamically create an array that I can later access.

e.g. as is my code will keep overwriting the Array_Two_New array with the first value the string in Array_Two(x,1) is split into.

I thought I could add Array_Two_New(x) in my code so that I get a result like:

Array_Two_New(1) = ("apple", "orange")
Array_Two_New(2) = ("pear", "banana")

And I could later access the value I want e.g.

Array_Two_New(1)(1) = "apple"
Array_Two_New(1)(2) = "orange"
Array_Two_New(2)(1) = "pear" // etc.

I think I'm confusing the syntax here.

I think I need to use an object but I am not too familiar with them.

I found these articles but I couldn't follow the explanations.

VBA: Split cell values into multiple rows and keep other data
Splitting an array into multiple rows

You could have a loop through A which splits "apple, orange" and then an inner loop which splits B "red, green" ? `Split` produces an array. — SJR, Oct 16 '19 at 15:33
So I have two different loops to split the cells in the columns that have comma separated entries. The problem is that each column will have multiple cells I need to split, and I need to split cells from multiple columns. — Kamui, Oct 16 '19 at 15:45
So I think I need two arrays - one for storing each cell value in a given column range, and one for storing the results from the cells I split. My problem is once I split the cells, I don't know how to best store the elements in this new second array and later access them. — Kamui, Oct 16 '19 at 15:45
e.g. If I initially get one array that contains "apple, orange", "banana, pear, grape" as two elements, I want to create a new array "apple", "orange" that has two elements and another array "banana", "pear" that contains three elements. But I'll need more than just two new arrays - I'll need to keep creating a new array every time I split a cell and be able to access its elements at a later point. Is there a way to do this? — Kamui, Oct 16 '19 at 15:46
I've posted an answer below so give it a try and let me know how you get on. — SJR, Oct 16 '19 at 15:46

score 3 · Accepted Answer · answered Oct 16 '19 at 15:42

This works, based on your example.

If you have a lot of data, you might want to consider writing your data into an array and only reading back once you've done the concatentation as this will speed things up considerably.

Sub x()

Dim vA As Variant, vB As Variant, i As Long, j As Long, r As Range

For Each r In Range("A1:A10")
    vA = Split(r, ",")
    vB = Split(r.Offset(, 1), ",")
    For i = LBound(vA) To UBound(vA)
        For j = LBound(vB) To UBound(vB)
            Range("F" & Rows.Count).End(xlUp)(2) = Trim(vA(i)) & "-" & Trim(vB(j)) & "-" & r.Offset(, 2).Value & "-" & r.Offset(, 3).Value
        Next j
    Next i
Next r

End Sub

Sorry for the delay... was tweaking it to my real program and I got it to work. Thanks so much for your help! :) — Kamui, Oct 16 '19 at 16:27

How to split a cell value into multiple rows while keeping other cell data?

1 Answers1