Comma separated Rows

D

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]
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy the column cells ("B:N") into the first empty row.
Replies
5
Views
283
mumps
mumps
Q
  • Question Question
Automatically insert new rows with predefined values
Replies
1
Views
789
quattroformatee
Q
SaraWitch
  • Solved
Join text and return dates where there is one but ignore if empty cells
2
Replies
12
Views
1K
SaraWitch
SaraWitch
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13

Forum statistics

Threads
1,226,730
Messages
6,192,699
Members
453,747
Latest member
tylerhyatt04

Share this page

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