Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert a table to list?
Can anybody help me to convert a table to a list?
For example: Table: Red Black VW 100 20 Toyota 15 50 to become: List: VW Red 100 VW Black 20 Toyota Red 15 Toyota Black 50 Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to convert a table to list?
Here is some VBA to do it
Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long, j As Long Dim iLastRow As Long Dim iLastCol As Long Application.ScreenUpdating = False With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 2 Step -1 iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column For j = iLastCol To 3 Step -1 .Rows(i + 1).Insert .Cells(i + 1, TEST_COLUMN).Value = .Cells(i, TEST_COLUMN).Value .Cells(i + 1, 2).Value = .Cells(i, j).Value .Cells(i, j).Value = "" Next j Next i .Rows(1).Delete End With Application.ScreenUpdating = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ferdi" wrote in message ... Can anybody help me to convert a table to a list? For example: Table: Red Black VW 100 20 Toyota 15 50 to become: List: VW Red 100 VW Black 20 Toyota Red 15 Toyota Black 50 Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert vertical blocked list to horizontal list? | Excel Worksheet Functions | |||
How to convert existing Excel List into a table in Access? | New Users to Excel | |||
Convert Pivot Table to Normal Data table | Excel Discussion (Misc queries) | |||
Convert excel table to HTML | Excel Discussion (Misc queries) | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |