display repeating row information in columns

  • Thread starter the.netherlands
  • Start date
T

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top