Comma separated Rows

[DSP VARMA]

Question
Col A	Col B	Col C	Col D
1	Angry Birds , Gaming	Android, IOS	L1,L2,L3
2	Nirvana,Rock,Band	Y,Z,A	M1,M2,M3
Please find the question on the top and the expected result at the bottom
There is no limit on the number of comma separated values .
Can somebody help me with VBA Code
This is a sample file

<tbody>
</tbody>

Result
Col A	Col B	Col C	Col D
1	Angry Birds	Android	L1
1	Angry Birds	Android	L2
1	Angry Birds	Android	L3
1	Angry Birds	IOS	L1
1	Angry Birds	IOS	L2
1	Angry Birds	IOS	L3
1	Gaming	Android	L1
1	Gaming	Android	L2
1	Gaming	Android	L3
1	Gaming	Android	L4
1	Gaming	IOS	L1
1	Gaming	IOS	L2
1	Gaming	IOS	L3
1	Gaming	IOS	L4
1	Gaming	Windows	L1
1	Gaming	Windows	L2
1	Gaming	Windows	L3
1	Gaming	Windows	L4
2	Nirvana	Y	M1
2	Nirvana	Y	M2
2	Nirvana	Y	M3
2	Nirvana	Z	M1
2	Nirvana	Z	M2
2	Nirvana	Z	M3
2	Nirvana	A	M1
2	Nirvana	A	M2
2	Nirvana	A	M3
2	Rock	Y	M1
2	Rock	Y	M2
2	Rock	Y	M3
2	Rock	Z	M1
2	Rock	Z	M2
2	Rock	Z	M3
2	Rock	A	M1
2	Rock	A	M2
2	Rock	A	M3
2	Band	Y	M1
2	Band	Y	M2
2	Band	Y	M3
2	Band	Z	M1
2	Band	Z	M2
2	Band	Z	M3
2	Band	A	M1
2	Band	A	M2
2	Band	A	M3

<tbody>
</tbody>

 

[MickG]

Try this for results on sheet2:-

[COLOR="Navy"]Sub[/COLOR] MG01Aug37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Sp1 [COLOR="Navy"]As[/COLOR] Variant, Sp2 [COLOR="Navy"]As[/COLOR] Variant, Sp3 [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nnn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray(), c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp1 = Split(Dn.Offset(, 1).Value, ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp1)
  Sp2 = Split(Dn.Offset(, 2).Value, ",")
    [COLOR="Navy"]For[/COLOR] nn = 0 To UBound(Sp2)
       Sp3 = Split(Dn.Offset(, 3).Value, ",")
        [COLOR="Navy"]For[/COLOR] nnn = 0 To UBound(Sp3)
            c = c + 1
            ReDim Preserve Ray(1 To 4, 1 To c)
            Ray(1, c) = Dn.Value
            Ray(2, c) = Sp1(n)
            Ray(3, c) = Sp2(nn)
            Ray(4, c) = Sp3(nnn)
        [COLOR="Navy"]Next[/COLOR] nnn
    [COLOR="Navy"]Next[/COLOR] nn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = Application.Transpose(Ray)
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[DSP VARMA]

Wonderful job. Thanks a Ton Mick, That is what exactly I required.

 

[DSP VARMA]

Hi Mick, If we have 7 to 10 columns instead of 4 what will be the code?

 

[MickG]

This is for 7 columns. You will see by comparison how you need to increase the number of loops/columns in array "Ray", relative to the number of columns required.

[COLOR="Navy"]Sub[/COLOR] MG02Aug09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Sp1 [COLOR="Navy"]As[/COLOR] Variant, Sp2 [COLOR="Navy"]As[/COLOR] Variant, Sp3 [COLOR="Navy"]As[/COLOR] Variant, Sp4 [COLOR="Navy"]As[/COLOR] Variant, sp5 [COLOR="Navy"]As[/COLOR] Variant, sp6 [COLOR="Navy"]As[/COLOR] Variant, sp7 [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n3 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n4 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n5 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n6 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray(), c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp1 = Split(Dn.Offset(, 1).Value, ",")
[COLOR="Navy"]For[/COLOR] n1 = 0 To UBound(Sp1)
  Sp2 = Split(Dn.Offset(, 2).Value, ",")
    [COLOR="Navy"]For[/COLOR] n2 = 0 To UBound(Sp2)
       Sp3 = Split(Dn.Offset(, 3).Value, ",")
        [COLOR="Navy"]For[/COLOR] n3 = 0 To UBound(Sp3)
          Sp4 = Split(Dn.Offset(, 4).Value, ",")
            [COLOR="Navy"]For[/COLOR] n4 = 0 To UBound(Sp4)
                sp5 = Split(Dn.Offset(, 5).Value, ",")
                    [COLOR="Navy"]For[/COLOR] n5 = 0 To UBound(sp5)
                        sp6 = Split(Dn.Offset(, 6).Value, ",")
                          [COLOR="Navy"]For[/COLOR] n6 = 0 To UBound(sp6)
                            c = c + 1
                            ReDim Preserve Ray(1 To 7, 1 To c)
                            Ray(1, c) = Dn.Value
                            Ray(2, c) = Sp1(n1)
                            Ray(3, c) = Sp2(n2)
                            Ray(4, c) = Sp3(n3)
                            Ray(5, c) = Sp4(n4)
                            Ray(6, c) = sp5(n5)
                            Ray(7, c) = sp6(n6)
                        [COLOR="Navy"]Next[/COLOR] n6
                 [COLOR="Navy"]Next[/COLOR] n5
            [COLOR="Navy"]Next[/COLOR] n4
        [COLOR="Navy"]Next[/COLOR] n3
  [COLOR="Navy"]Next[/COLOR] n2
[COLOR="Navy"]Next[/COLOR] n1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet20").Range("A1").Resize(c, 7)
    .Value = Application.Transpose(Ray)
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mic

 

[DSP VARMA]

Thank You very much Mick. Great Job

 

[MickG]

You're welcome