Separating lists of data in one cell to multiple rows

[shebe228]

I received a spreadsheet that was submitted from an outside source. I have invoices in one column then the line items of that invoice all in one cell to the right, then the corresponding charges. Is there any way to separate the codes and charges to be in separate cells on multiple rows?


There is no space between the codes to do a find/replace then text to columns. Each invoice has varying number of codes/charges

This is what I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]Code[/TD]
[TD]Charges[/TD]
[/TR]
[TR]
[TD]79851321987[/TD]
[TD]97110
97140
aa75861[/TD]
[TD]90.00
70.00
8.00[/TD]
[/TR]
[TR]
[TD]654321884[/TD]
[TD]asdf83476
97082[/TD]
[TD]155.00
2.22[/TD]
[/TR]
[TR]
[TD]6546513547987[/TD]
[TD]97124
97812
97124
79124
97127
94157
65486
65489[/TD]
[TD]100.00
500.00
50.00
50.00
50.00
50.00
50.00
50.00[/TD]
[/TR]
</tbody>[/TABLE]




This is what I want:[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]Code[/TD]
[TD]Charges[/TD]
[/TR]
[TR]
[TD]79851321987[/TD]
[TD]97110[/TD]
[TD]90.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97140[/TD]
[TD]70.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]aa75861[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]654321884[/TD]
[TD]asdf83476[/TD]
[TD]155.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97082[/TD]
[TD]2.22[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

In an unused cell enter
=CODE(MID(B2,6,1))
change the B2 to point at the cell that contains
97110
97140
aa75861

What does the formula return

 

[shebe228]

It returns "10".

 

[shebe228]

I was able to get the separate the data into multiple columns. Now I need to transpose those columns back to one column. The varying number of columns on each row is what is throwing me off. Suggestions?

My data looks like this:
[TABLE="width: 624"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]97110[/TD]
[TD="align: right"]97140[/TD]
[TD]AAGRT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]99213[/TD]
[TD]AAGRT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CDE[/TD]
[TD]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD="align: right"]97124[/TD]
[TD]AAGRT[/TD]
[/TR]
</tbody>[/TABLE]



and I want it to look like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]97110[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97140[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AAGRT[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]99213[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AAGRT[/TD]
[/TR]
[TR]
[TD]CDE[/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]97124[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AAGRT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub Splitrws()
   Dim i As Long, x As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      x = Application.Max(UBound(Split(Cells(i, 2), Chr(10))), UBound(Split(Cells(i, 3), Chr(10))))
      If x > 0 Then
         Rows(i + 1).Resize(x).Insert
         Cells(i, 2).Resize(x + 1).Value = Application.Transpose(Split(Cells(i, 2), Chr(10)))
         Cells(i, 3).Resize(x + 1).Value = Application.Transpose(Split(Cells(i, 3), Chr(10)))
      End If
   Next i
End Sub

Run this on your original data.

 

[shebe228]

How about

Sub Splitrws()
   Dim i As Long, x As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      x = Application.Max(UBound(Split(Cells(i, 2), Chr(10))), UBound(Split(Cells(i, 3), Chr(10))))
      If x > 0 Then
         Rows(i + 1).Resize(x).Insert
         Cells(i, 2).Resize(x + 1).Value = Application.Transpose(Split(Cells(i, 2), Chr(10)))
         Cells(i, 3).Resize(x + 1).Value = Application.Transpose(Split(Cells(i, 3), Chr(10)))
      End If
   Next i
End Sub

Run this on your original data.

Thanks!! I took the long way around, but it worked. Thank you for your help!!

 

[Fluff]

You're welcome