Copy rows based on a delimiter in each column

[Himaja]

I have a requirement as below: Each row might have multiple data (comma seperated). Based on comma as delimiter, I want to have those many rows inserted below and each row should have one color and one data combination. Can someone please help me on this.
In the below example,where Red has 3 data elements (a1,a2, a3) -> It should create 2 more rows and copy Red and have one data element for each row.

Either it can copy in the same sheet or have the o/p table in a new sheet. Appreciate help on this

[TABLE="width: 133"]
<tbody>[TR]
[TD]Col1[/TD]
[TD]Col2[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]A1, A2, A3[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]A4, A5[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]A1, A2[/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]A6[/TD]
[/TR]
</tbody>[/TABLE]

Output should be:
[TABLE="width: 133"]
<tbody>[TR]
[TD]Col11[/TD]
[TD]Col2[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]A6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Hi, Himaja
Try this code:
I assumed the number of row as the result won't exceed 10 times of the original one. So, say, your data is 500 rows then the result won't exceed 5000 rows. If that's not the case then we need to tweak the code a bit.
I put the result in D1.

[B][color=Royalblue]Sub[/color][/B] a1075938a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1075938-copy-rows-based-delimiter-each-column.html[/color][/i]
[B][color=Royalblue]Dim[/color][/B] i [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], j [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B]
[B][color=Royalblue]Dim[/color][/B] va [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], vb [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], ac [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], z [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B]

va = Range([color=brown]"A1:B"[/color] & Cells(Rows.count, [color=brown]"A"[/color]).[B][color=Royalblue]End[/color][/B](xlUp).row)
[B][color=Royalblue]ReDim[/color][/B] vb([color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] UBound(va, [color=crimson]1[/color]) * [color=crimson]10[/color], [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] [color=crimson]2[/color])

[B][color=Royalblue]For[/color][/B] i = [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] UBound(va, [color=crimson]1[/color])
ac = Split(va(i, [color=crimson]2[/color]), [color=brown]","[/color])
    [B][color=Royalblue]For[/color][/B] [B][color=Royalblue]Each[/color][/B] z [B][color=Royalblue]In[/color][/B] ac
        j = j + [color=crimson]1[/color]
        vb(j, [color=crimson]1[/color]) = va(i, [color=crimson]1[/color])
        vb(j, [color=crimson]2[/color]) = Trim(z)
    [B][color=Royalblue]Next[/color][/B]
[B][color=Royalblue]Next[/color][/B]
Range([color=brown]"D1"[/color]).Resize(j, [color=crimson]2[/color]) = vb
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]

RESULT

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Red[/td][td]A1, A2, A3[/td][td][/td][td]Red[/td][td]A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Yellow[/td][td]A4, A5[/td][td][/td][td]Red[/td][td]A2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Green[/td][td]A1, A2[/td][td][/td][td]Red[/td][td]A3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]White[/td][td]A6[/td][td][/td][td]Yellow[/td][td]A4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td][/td][td][/td][td][/td][td]Yellow[/td][td]A5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td][/td][td][/td][td]Green[/td][td]A1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td][/td][td]Green[/td][td]A2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td][/td][td][/td][td][/td][td]White[/td][td]A6[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]