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!

 

[Rick Rothstein]

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>
</SPAN>

Hi Robert,

I think this code does what you are asking for above. I showed the additional code I added to handle your request in red in case you want to try changing it for future needs.

Sub RedistributeData()
  Dim X As Long, LastRow As Long, A As Range, Table As Range, Cell As Range, Data() As String
  Const Delimiter As String = ", "
  Const DelimitedColumn As String = "I"
  Const TableColumns As String = "A:Y"
  Const StartRow As Long = 2
  Const NumberColumnRangesToSplit As String = "R:R,Y:Y"
  Application.ScreenUpdating = False
  LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  For X = LastRow To StartRow Step -1
    Data = Split(Cells(X, DelimitedColumn), Delimiter)
    If UBound(Data) Then
      Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
    End If
    Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
    For Each Cell In Intersect(Range(NumberColumnRangesToSplit), Rows(X))
      Cell.Resize(UBound(Data) + 1).Value = Cell.Value / (UBound(Data) + 1)
    Next
  Next
  LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
  On Error GoTo NoBlanks
  Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
  On Error GoTo 0
  For Each A In Table.SpecialCells(xlBlanks).Areas
    A.FormulaR1C1 = "=R[-1]C"
    A.Value = A.Value
  Next
NoBlanks:
  Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

I didn't change the start row to 1, it still says 2.

What row does your data (not headers) start on?

 

[Robert Mika]

Thank you Rick.
Almost there.
The code divide values in columns N-Y accorrding to column "I" but it leaves empty cells in columns A-M(I have text here)(excluding I) under each original row.
I hope that makes sense.
(if not I will supply an example)
I have change:

Const NumberColumnRangesToSplit As String = "R:R,Y:Y"

to

Const NumberColumnRangesToSplit As String = "N:Y"

What I'm doing wrong?
Amended code

 

[Rick Rothstein]

Thank you Rick.
Almost there.
The code divide values in columns N-Y accorrding to column "I" but it leaves empty cells in columns A-M(I have text here)(excluding I) under each original row.
I hope that makes sense.
(if not I will supply an example)
I have change:

Const NumberColumnRangesToSplit As String = "R:R,Y:Y"

to

Const NumberColumnRangesToSplit As String = "N:Y"

What I'm doing wrong?

I set up a test sheet with headers in A1:Y1, filled A:H,J:M with three rows of text, filled N:Y with three rows of numbers and put varying text in three rows of Column I using a comma-space as the delimiter. Then I made the change to the NumberColumnRangesToSplit constant that you indicated above and everything worked as expected... the text in Column I split down the column as expected, the text in each row in A:H,J:M for each row duplicated along with the split-out Column I text for that row and the numbers divided evenly along with the split-out Column I text as well. From my vantage point, everything appears to work as you indicated you wanted. If you want me to send you my sample, email me at rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbols they spell out). Oh, and tell me the version of Excel that you are using.

 

[ngrosser]

Hi Rick,
it nearly works fine for me but instead of copying the corresponsding cell values I get the literal content "=R[-1]C".
It looks like some kind of formula that should actually copy the cells, in your code fragment:

A.FormulaR1C1 = "=R[-1]C"
A.Value = A.Value

Could it be a problem with the locale and I need other quotes or something? (I'm on a Dutch system...)

 

[Rick Rothstein]

Hi Rick,
it nearly works fine for me but instead of copying the corresponsding cell values I get the literal content "=R[-1]C".
It looks like some kind of formula that should actually copy the cells, in your code fragment:

A.FormulaR1C1 = "=R[-1]C"
A.Value = A.Value

Could it be a problem with the locale and I need other quotes or something? (I'm on a Dutch system...)

I know nothing about international/locale issues, so I do not know how to answer that question. However, the code I posted was tested prior to being posted and it worked on my computer, so locale settings could very well be some kind of issue. Hopefully some one with experience in non-US locales will see your message and join this thread. Sorry I cannot be of more help to you.

 

[ngrosser]

I know nothing about international/locale issues, so I do not know how to answer that question. However, the code I posted was tested prior to being posted and it worked on my computer, so locale settings could very well be some kind of issue. Hopefully some one with experience in non-US locales will see your message and join this thread. Sorry I cannot be of more help to you.

Thank you Rick,
it works perfectly now. My spreadsheet had been exported from a SharePoint environment and all of the cells were formatted as "text". Using category "general" solved the issue.

 

[GLO4Excel]

Hello Rick,
Does your code allow multiple delimited columns?
<code>Const DelimitedColumn As String = "C-U"
</code><code>Would it split these multiple columns correctly for any given row when each delimited column can have 1-n items delimited by comma?
Your feedback would be greatly appreciated.
thank you.</code><code></code><code></code>

 

[ZaXaZ]

Hi i have a similar problem but i don´t know how to edit the macro to fit my data, can some one help?

i got this big list´s of what a item its on of and it is serves to me in txt files in a stupid way with lots of sorting needet to be done.

type Model
405 101 103 121 123 201 221
900 911 912 913 917 918 919 922 934 936 937 938
list goes one feels like its endless..

i need it to be made to
type Model
405 101
405 102
405 121
405 123
405 201
405 221

900 911
900 912
900 913
900 917
900 918
900 919
900 922
900 934
900 936
900 937
900 938