Splitting a single rows data into multiple rows

[wizardhelp]

Hi,

I'm relatively new to the excel world and I'm stuck! A solution in either excel or access would be greatly appreciated and save me a lot of useless copy/paste.

Here's what the problem looks like right now:

-- removed inline image ---

<table border="0" cellpadding="0" cellspacing="0" width="493"><colgroup><col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:11300;width:232pt" width="309"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:72pt" align="right" height="20" width="96">Col. A
</td> <td class="xl65" style="width:66pt" align="left" width="88"> Col. B
</td> <td style="width:232pt" width="309"> Col. C
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">13291</td> <td class="xl65" align="right"> 25-May-12</td> <td> 2309122,2309130,2309149,2309157</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">13292</td> <td class="xl65" align="right">25-May-12</td> <td> 2308363,2287927,2287935,2287943</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">13293</td> <td class="xl65" align="right">25-May-12</td> <td> 2385015,2385023,2385031</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">13294</td> <td class="xl65" align="right">25-May-12
</td> <td> 2352141</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">13307</td> <td class="xl65" align="right">25-May-12</td> <td> 2386968,2386976,2386984</td> </tr> </tbody></table>

I want it to look like this:

-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" height="340" width="333"><colgroup><col style="mso-width-source:userset;mso-width-alt:1536;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:8045;width:165pt" width="220"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:32pt" height="20" width="42">
</td> <td class="xl66" style="width:53pt" width="71">
</td> <td class="xl65" style="width:165pt" width="220">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Col. A
</td> <td class="xl66"> Col. B
</td> <td class="xl65">Col C.
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13291</td> <td class="xl66"> 25-May-12
</td> <td class="xl65">2309122</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13291</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2309130</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13291</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2309149</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13291</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2309157</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13292</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2308363</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13292</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2287927</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13292</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2287935</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13292</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2287943</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13293</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2385015</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13293</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2385023</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13293</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2385031</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13294</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2352141</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13307</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2386968</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13307</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2386976</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">13307</td> <td class="xl66"> 25-May-12</td> <td class="xl65">2386984</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>

I have 1000s of rows, so doing this by hand isn't exactly feasible. Any help would be great!! (Even if it's just to tell me that I can't do this)

... Hopefully these tables can be understood. It wouldn't let me just copy and paste a picture!

Thank you!

 

[Warship]

is a macro (vba) ok?

 

[wizardhelp]

Yes! Thank you!

 

[mathchick]

It looks like text-to-columns would work, which wouldn't require any VBA

 

[Tinbendr]

This puts the results in DEF.

Sub ParseRow()

Dim WS As Worksheet
Dim aCell As Range
Dim A As Long
Dim MyArray As Variant
Dim LastRow As Long
Dim Counter As Long

Set WS = Sheet2

Counter = 1

With WS
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For Each aCell In .Range("C2:C" & LastRow)
    MyArray = Split(aCell.Value, ",")
    For A = 0 To UBound(MyArray)
        Counter = Counter + 1
        .Range("D" & Counter).Value = CStr(aCell.Offset(, -2))
        .Range("E" & Counter).Value = CStr(aCell.Offset(, -1))
        .Range("F" & Counter).Value = CStr(MyArray(A))
    Next
Next

End With

 

[Rick Rothstein]

Yes! Thank you!

You can use the code in my mini-blog article here...

Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

to do that. The only things in the posted code you might have to change are two constants (in the Const statements). The first is the Delimiter constant... change the comma/space (", ") to just a comma (","). The second is the StartRow... this is the start row for your data... the article assumes it is 2 (making way for a header in Row 1), but you can change that 2 to a 1 if your data actually starts on Row 1.

 

[Warship]

on a new sheet:

Sub SplitData()
    Dim arrColC As Variant
    Dim shDATA As Worksheet
    Dim r As Long, c As Long, i As Long
    
    Set shDATA = Sheets("Data Sheet") 'change as needed
    
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("SPLIT SHEET").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=shDATA
    ActiveSheet.Name = "SPLIT SHEET"
    
    i = 1
    For r = 1 To shDATA.Cells(Rows.Count, "A").End(xlUp).Row
        arrColC = Split(shDATA.Cells(r, 3), ",")
        For c = 0 To UBound(arrColC)
            Cells(i, 1) = shDATA.Cells(r, 1)
            Cells(i, 2) = Format(shDATA.Cells(r, 2), "d-mmm-yy")
            Cells(i, 3) = arrColC(c)
            i = i + 1
        Next c
    Next r
End Sub

 

[wizardhelp]

Thank you!! I'll try these tomorrow and let you know how it goes!

 

[wizardhelp]

In response to Rick Rothstein:http://www.mrexcel.com/forum/member.php?u=178213

Thank you!
It seemed to be working. I only thing I initially changed was the delimiter constant from ", " to "," as you suggested. And it worked perfectly. The delimiter cells were in column C and the start row was 2. However, I have "A:L" columns. Without touching any of the other code, I changed this:

Const TableColumns As String = "A:C"

to

Const TableColumns As String = "A:L"

But when I ran that code again, it didn't work. It copied all of the headers throughout the entire worksheet to look like this all the way through the worksheet:<table style="width: 1px; height: 4px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height:15.0pt" height="20"><td class="xl65" style="height:15.0pt;width:72pt" height="20" width="96">
</td><td class="xl64" style="width:56pt" width="75">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl65" style="height:15.0pt" height="20">
</td><td class="xl64"> <table border="0" cellpadding="0" cellspacing="0" width="420"><colgroup><col style="width:26pt" width="35"> <col style="width:26pt" span="2" width="35"> <col style="width:26pt" span="9" width="35"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:26pt" height="20" width="35">A</td> <td class="xl66" style="width:26pt" width="35">B</td> <td class="xl66" style="width:26pt" width="35">C</td> <td class="xl67" style="width:26pt" width="35">D</td> <td class="xl67" style="width:26pt" width="35">E</td> <td class="xl67" style="width:26pt" width="35">F</td> <td class="xl67" style="width:26pt" width="35">G</td> <td class="xl67" style="width:26pt" width="35">H</td> <td class="xl67" style="width:26pt" width="35">I</td> <td class="xl67" style="width:26pt" width="35">J</td> <td class="xl67" style="width:26pt" width="35">K</td> <td class="xl67" style="width:26pt" width="35">L</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">A</td> <td class="xl66">B</td> <td class="xl66">C</td> <td class="xl67">D</td> <td class="xl67">E</td> <td class="xl67">F</td> <td class="xl67">G</td> <td class="xl67">H</td> <td class="xl67">I</td> <td class="xl67">J</td> <td class="xl67">K</td> <td class="xl67">L</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">A</td> <td class="xl66">B</td> <td class="xl66">C</td> <td class="xl67">D</td> <td class="xl67">E</td> <td class="xl67">F</td> <td class="xl67">G</td> <td class="xl67">H</td> <td class="xl67">I</td> <td class="xl67">J</td> <td class="xl67">K</td> <td class="xl67">L</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">A</td> <td class="xl66">B</td> <td class="xl66">C</td> <td class="xl67">D</td> <td class="xl67">E</td> <td class="xl67">F</td> <td class="xl67">G</td> <td class="xl67">H</td> <td class="xl67">I</td> <td class="xl67">J</td> <td class="xl67">K</td> <td class="xl67">L</td> </tr> </tbody></table></td></tr><tr style="height:15.0pt" height="20"><td class="xl65" style="height:15.0pt" height="20">
</td><td class="xl64">
</td></tr><tr style="height:15.0pt" height="20"><td class="xl65" style="height:15.0pt" height="20">
</td><td class="xl64">
</td></tr></tbody></table>I didn't change the start row to 1, it still says 2. So I assume I should be changing something else in the code somewhere.

Thanks for your help!

 

[Robert Mika]

You can use the code in my mini-blog article here...

Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

to do that. The only things in the posted code you might have to change are two constants (in the Const statements). The first is the Delimiter constant... change the comma/space (", ") to just a comma (","). The second is the StartRow... this is the start row for your data... the article assumes it is 2 (making way for a header in Row 1), but you can change that 2 to a 1 if your data actually starts on Row 1.

Hi Rick,

How we can amend this code to do another task:</SPAN>
My data table - “A-Y”, column “ I” is the column with coma delimited values, columns N-Y contains numerical values (includes 0 or empty cell)</SPAN>
I would like to divide those numerical values in each rows depends on how many coma delimited values there are in column "I"</SPAN>
Example:</SPAN>
One row in Column "I" has "A, B, C" the value in column "R" is 60 and in column "Y" is 150, after split 3 rows with 20 and 50 in each respectively.</SPAN>

Thank you.</SPAN>