display repeating row information in columns

[the.netherlands]

How can I display information that repeats in rows, in columns?

Everything is driven in this case by account. The problem is that account could be repeated between 1 and 22 times. For each of these (up to 22) I need a column for Code1 (1) through Code1 (22) and Code2 (1) through Code2 (22)

Example:

Account | Code 1 | Code 2
123 | 1 | 2
345 | 3 | 2
345 | 3 | 3
456 | 1 | 2
456 | 1 | 4
456 | 1 | 3

 

[the.netherlands]

First, sort your data by name, and remove any blank rows (if
any). Then, run the following code.

The code makes the following assumptions:
1) Source data is on Sheet1, column A, with no blanks.
2) The transformed data is written to Sheet2.
3) No single name has more than 255 accounts.

Sub XForm()

Dim SRng As Range
Dim DRng As Range
Dim SaveVal As String

Set SRng = Worksheets("Sheet1").Range("A1")
Set DRng = Worksheets("Sheet2").Range("A1")

Do Until SRng.Value = ""
If SRng.Value <> SaveVal Then
Set DRng = DRng.Parent.Cells(DRng.Row + 1, 1)
DRng.Value = SRng.Value
SaveVal = SRng.Value
End If
Set DRng = DRng(1, 2)
DRng.Value = SRng(1, 2).Value
Set SRng = SRng(2, 1)
Loop

End Sub