Column to Rows (not transpose)

[TomOold]

Hi all,

I have a document (export from DB) with order numbers and in the column to the right all the shipping codes for this order.
I need to have the shipping codes (with ordernumber) on seperate rows (see table below).

The shipping codes from column b to different columns is something I can do, but I do not know how to (automattically) put it to different rows with the order number in front of it.

Could you please help me?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Ordernr.[/TD]
[TD]Shipping Codes[/TD]
[/TR]
[TR]
[TD]2017001[/TD]
[TD]Shipping 1, Shipping 2, Shipping 3[/TD]
[/TR]
[TR]
[TD]2017002[/TD]
[TD]Shipping 4, Shipping 5[/TD]
[/TR]
[TR]
[TD]2017003[/TD]
[TD]Shipping 7, Shipping 8, Shipping 9, Shipping 10, Shipping 11[/TD]
[/TR]
</tbody>[/TABLE]
etc

to [TABLE="width: 500"]
<tbody>[TR]
[TD]Ordernr[/TD]
[TD]Shipping Code[/TD]
[/TR]
[TR]
[TD]2017001[/TD]
[TD]Shipping 1[/TD]
[/TR]
[TR]
[TD]2017001[/TD]
[TD]Shipping 2[/TD]
[/TR]
[TR]
[TD]2017001[/TD]
[TD]Shipping 3[/TD]
[/TR]
[TR]
[TD]2017002[/TD]
[TD]Shipping 4[/TD]
[/TR]
[TR]
[TD]2017002[/TD]
[TD]Shipping 5[/TD]
[/TR]
[TR]
[TD]2017003[/TD]
[TD]Shipping 7[/TD]
[/TR]
[TR]
[TD]2017003[/TD]
[TD]Shipping 8[/TD]
[/TR]
[TR]
[TD]2017003[/TD]
[TD]Shipping 9[/TD]
[/TR]
[TR]
[TD]2017003[/TD]
[TD]Shipping 10[/TD]
[/TR]
</tbody>[/TABLE]
Etc.

 

[Fluff]

Hi & welcome to the board.
How about

Sub SplitTranspose()

   Dim Cl As Range
   Dim Rws As Long
   
   Range("D1:E1").Value = Range("A1:B1").Value
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Rws = UBound(Split(Cl.Offset(, 1), ",")) + 1
      With Range("D" & Rows.Count).End(xlUp).Offset(1)
         .Resize(Rws).Value = Cl.Value
         .Offset(, 1).Resize(Rws).Value = Application.Transpose(Split(Cl.Offset(, 1), ","))
      End With
   Next Cl

End Sub

 

[Kaper]

If the output is comma separated, one of possible (semi)automated solutions could be:
1. change it into separate cells with text-to-columns tool,
2. Unpivot data (see for examle how to use Multiple Consolidation Ranges PivotTable for it here: https://superuser.com/questions/78439/how-to-unpivot-or-reverse-pivot-in-excel
3. Remove empty column B entries (with autofilter)