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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
228
mumps
mumps
Q
  • Question Question
Automatically insert new rows with predefined values
Replies
1
Views
737
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,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

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