Transpose irregular data from columns to rows

[slow1911s]

I have a two column set of data with ZIP codes in one and order numbers in the second. In some ZIP codes there is only one order number, but some have two and up to six.

I have used the Copy/Paste Special-Transpose, but this worksheet has hundreds of rows and is quite time consuming. Is there another way I'm not considering?

It looks like this:

What I have

	A	B
	12345	Order1
		Order2
1		Order3
2	45678	Order4
3	98765	Order5
4		Order6
5	34567	Order7
6	13579	Order8
7		Order9
8		Order10
9		Order11

<tbody>
</tbody>

What I need

	A	B	C	D	E
1	12345	Order1	Order2	Order3
2	45678	Order4
3	98765	Order5	Order6
4	34567	Order7
5	13579	Order8	Order9	Order10	Order11

<tbody>
</tbody>

 

[Mindpsyche]

Try this I have used 2 helper columns though:

Excel 2003
	A	B	C	D	E
1	Number	Order	Helper 1	Helper 2
2	12345	Order 1	12345	12345 - 1
3		Order 2	12345	12345 - 2
4		Order 3	12345	12345 - 3
5	45678	Order 1	45678	45678 - 1
6		Order 2	45678	45678 - 2
7		Order 3	45678	45678 - 3
8	34567	Order 1	34567	34567 - 1
9		Order 2	34567	34567 - 2
10		Order 3	34567	34567 - 3
11		Order 4	34567	34567 - 4
12
13
14
15
16	Final Result
17	Number
18	12345	Order 1	Order 2	Order 3	#N/A
19	45678	Order 1	Order 2	Order 3	#N/A
20	34567	Order 1	Order 2	Order 3	Order 4
Sheet1

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(A2:A2,A2)=1,A2,C1)
C3	=IF(COUNTIF(A3:A3,A3)=1,A3,C2)
C4	=IF(COUNTIF(A4:A4,A4)=1,A4,C3)
C5	=IF(COUNTIF(A5:A5,A5)=1,A5,C4)
C6	=IF(COUNTIF(A6:A6,A6)=1,A6,C5)
C7	=IF(COUNTIF(A7:A7,A7)=1,A7,C6)
C8	=IF(COUNTIF(A8:A8,A8)=1,A8,C7)
C9	=IF(COUNTIF(A9:A9,A9)=1,A9,C8)
C10	=IF(COUNTIF(A10:A10,A10)=1,A10,C9)
C11	=IF(COUNTIF(A11:A11,A11)=1,A11,C10)
C18	=INDEX($B$2:$B$11,MATCH($A18&" - "&COLUMNS($B17:C17),$D$2:$D$11,0))
C19	=INDEX($B$2:$B$11,MATCH($A19&" - "&COLUMNS($B18:C18),$D$2:$D$11,0))
C20	=INDEX($B$2:$B$11,MATCH($A20&" - "&COLUMNS($B19:C19),$D$2:$D$11,0))
D2	=C2&" - "&COUNTIF(C$2:C2,C2)
D3	=C3&" - "&COUNTIF(C$2:C3,C3)
D4	=C4&" - "&COUNTIF(C$2:C4,C4)
D5	=C5&" - "&COUNTIF(C$2:C5,C5)
D6	=C6&" - "&COUNTIF(C$2:C6,C6)
D7	=C7&" - "&COUNTIF(C$2:C7,C7)
D8	=C8&" - "&COUNTIF(C$2:C8,C8)
D9	=C9&" - "&COUNTIF(C$2:C9,C9)
D10	=C10&" - "&COUNTIF(C$2:C10,C10)
D11	=C11&" - "&COUNTIF(C$2:C11,C11)
D18	=INDEX($B$2:$B$11,MATCH($A18&" - "&COLUMNS($B17:D17),$D$2:$D$11,0))
D19	=INDEX($B$2:$B$11,MATCH($A19&" - "&COLUMNS($B18:D18),$D$2:$D$11,0))
D20	=INDEX($B$2:$B$11,MATCH($A20&" - "&COLUMNS($B19:D19),$D$2:$D$11,0))
B18	=INDEX($B$2:$B$11,MATCH($A18&" - "&COLUMNS($B17:B17),$D$2:$D$11,0))
B19	=INDEX($B$2:$B$11,MATCH($A19&" - "&COLUMNS($B18:B18),$D$2:$D$11,0))
B20	=INDEX($B$2:$B$11,MATCH($A20&" - "&COLUMNS($B19:B19),$D$2:$D$11,0))
E18	=INDEX($B$2:$B$11,MATCH($A18&" - "&COLUMNS($B17:E17),$D$2:$D$11,0))
E19	=INDEX($B$2:$B$11,MATCH($A19&" - "&COLUMNS($B18:E18),$D$2:$D$11,0))
E20	=INDEX($B$2:$B$11,MATCH($A20&" - "&COLUMNS($B19:E19),$D$2:$D$11,0))

 

[mirabeau]

I have a two column set of data with ZIP codes in one and order numbers in the second. In some ZIP codes there is only one order number, but some have two and up to six.

I have used the Copy/Paste Special-Transpose, but this worksheet has hundreds of rows and is quite time consuming. Is there another way I'm not considering?

It looks like this:

What I have
...

Would you be happy to use a VBA macro?

Sub transpose_irregular()
Dim a, c(), p&, q&, i&
a = Cells(1).CurrentRegion.Resize(, 2)
ReDim c(1 To UBound(a, 1), 1 To 2)
For i = 1 To UBound(a, 1)
    If Len(a(i, 1)) > 0 Then
        p = p + 1: q = 2
        c(p, 1) = a(i, 1)
        c(p, 2) = a(i, 2)
    Else
        q = q + 1
        If q > UBound(c, 2) Then _
            ReDim Preserve c(1 To UBound(a, 1), 1 To q)
        c(p, q) = a(i, 2)
    End If
Next i
Cells(1).CurrentRegion.ClearContents
Cells(1).Resize(p, UBound(c, 2)) = c
For i = 1 To UBound(c, 2)
    Cells(1, i) = Chr(i + 64)
Next i
End Sub