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?

Ordernr.	Shipping Codes
2017001	Shipping 1, Shipping 2, Shipping 3
2017002	Shipping 4, Shipping 5
2017003	Shipping 7, Shipping 8, Shipping 9, Shipping 10, Shipping 11

<tbody>
</tbody>

etc

to

Ordernr	Shipping Code
2017001	Shipping 1
2017001	Shipping 2
2017001	Shipping 3
2017002	Shipping 4
2017002	Shipping 5
2017003	Shipping 7
2017003	Shipping 8
2017003	Shipping 9
2017003	Shipping 10

<tbody>
</tbody>

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)