Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display repeating row information in columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display repeating row information in columns
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeating information throughout a worksheet | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
How to convert a repeating row of information to fit in 3 columns | Excel Worksheet Functions | |||
How to convert a repeating row of information to fit in 3 columns | Excel Worksheet Functions | |||
population list box without repeating information | Excel Programming |