Comma separated Rows

DSP VARMA

New Member
Joined
Dec 27, 2016
Messages
9
[TABLE="width: 687"]
<tbody>[TR]
[TD="colspan: 4"]Question
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A
[/TD]
[TD] Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds , Gaming
[/TD]
[TD]Android, IOS
[/TD]
[TD]L1,L2,L3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana,Rock,Band
[/TD]
[TD] Y,Z,A
[/TD]
[TD]M1,M2,M3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Please find the question on the top and the expected result at the bottom
[/TD]
[/TR]
[TR]
[TD="colspan: 4"]There is no limit on the number of comma separated values .
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Can somebody help me with VBA Code
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]This is a sample file
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 621"]
<tbody>[TR]
[TD="colspan: 4"]Result
[/TD]
[/TR]
[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]Android
[/TD]
[TD]L1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]Android
[/TD]
[TD]L2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]Android
[/TD]
[TD]L3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]IOS
[/TD]
[TD]L1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]IOS
[/TD]
[TD]L2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Angry Birds
[/TD]
[TD]IOS
[/TD]
[TD]L3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Android
[/TD]
[TD]L1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Android
[/TD]
[TD]L2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Android
[/TD]
[TD]L3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Android
[/TD]
[TD]L4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]IOS
[/TD]
[TD]L1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]IOS
[/TD]
[TD]L2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]IOS
[/TD]
[TD]L3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]IOS
[/TD]
[TD]L4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Windows
[/TD]
[TD]L1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Windows
[/TD]
[TD]L2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Windows
[/TD]
[TD]L3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gaming
[/TD]
[TD]Windows
[/TD]
[TD]L4
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Y
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Y
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Y
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Z
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Z
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]Z
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]A
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]A
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Nirvana
[/TD]
[TD]A
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Y
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Y
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Y
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Z
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Z
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]Z
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]A
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]A
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rock
[/TD]
[TD]A
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Y
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Y
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Y
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Z
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Z
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]Z
[/TD]
[TD]M3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]A
[/TD]
[TD]M1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]A
[/TD]
[TD]M2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Band
[/TD]
[TD]A
[/TD]
[TD]M3
[/TD]
[/TR]
</tbody>[/TABLE]
 
Try this for results on sheet2:-
Code:
[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
 
Upvote 0
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.
Code:
[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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top