How to convert a table to list?

F

ferdi

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
 
B

Bob Phillips

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)
 
Top