Transposition impossibility?

[Brisbane Rob]

When I import my inventory records from the accounting package this is what I have:-

Code Size
1 3
1 6
1 7
2 2
2 3
2 4
2 6
3 4
4 1
4 3

You get the picture - twelve pages of codes with different sizes.
This is what I need:-

Code Sizes
1 3 6 7
2 2 3 4 6
3 4
4 1 3

With a random number of lines being reported for the sizes for the product codes, I can't figure out a way to run a >copy>paste special>transpose macro because doing it manually sucks.

It would be best if the sizes went into different columns so I could run further analysis but any ideas gratefully accepted.

[Brisbane Rob]

My layout lost its formatting when I submitted it - the data comes into Excel in two columns, code and size.

[Stefan]

>With a random number of lines being reported for the sizes for the
>product codes, I can't figure out a way to run a >copy>paste
>special>transpose macro because doing it manually sucks.

>It would be best if the sizes went into different columns so I could
>run further analysis but any ideas gratefully accepted.

Rob,

you can run this code

Sub transform()
Set b = Worksheets("Sheet1") '< change name if needed
iRow = Application.CountA(Columns(1))
xRow = 1
xCol = 5
For i = 2 To iRow
If Cells(i, 1) <> Cells(i - 1, 1) Then
xRow = xRow + 1: xCol = 5
Cells(xRow, 4) = Cells(i, 1)
End If
Cells(xRow, xCol) = Cells(i, 2)
xCol = xCol + 1
Next
End Sub


This is if your list is in column A:B and starts from the top.
The result comes in column D: ...

--
Stefan

[Stefan]

"> Rob,
>
> you can run this code
>
> Sub transform()
> Set b = Worksheets("Sheet1") '< change name if needed
> iRow = Application.CountA(Columns(1))
> xRow = 1
> xCol = 5
> For i = 2 To iRow
> If Cells(i, 1) <> Cells(i - 1, 1) Then
> xRow = xRow + 1: xCol = 5
> Cells(xRow, 4) = Cells(i, 1)
> End If
> Cells(xRow, xCol) = Cells(i, 2)
> xCol = xCol + 1
> Next
> End Sub
>
>
> This is if your list is in column A:B and starts from the top.
> The result comes in column D: ...
>
> --
> Stefan


Sorry, first line (set b= ...) isn't necessary if the sheet is visible on
your screen; otherwise you must add b. before every Cells in the code.

Cells(... = b.Cells(...

[Brisbane Rob]

Many thanks Stephan. It works brilliantly...