Need To Change Horizontal Table to Vertical List

**jambo2000** · 07-22-2013, 12:12 PM

Hi

I need to change data from a horizontal table into a vertical list, can anybody help? example.xlsx

I searched the formum and found solutions to a similar problem but they don't quite work for this. This isn't one that transposing will fix

Thanks in advance.

**Special-K** · 07-22-2013, 12:42 PM

Try this
Also your data output is incorrect on sc04 blue m, it should be 1 not 2.

Sub k1()
Dim LastRow As Long, i, j, k, LastCol As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
k = 0
For i = 2 To 5
    For j = 3 To 7
        k = k + 1
        Sheets("Sheet2").Range("A1").Offset(k - 1, 0) = Sheets("Sheet1").Range("A1").Offset(i - 1, 0)
        Sheets("Sheet2").Range("A1").Offset(k - 1, 1) = Sheets("Sheet1").Range("A1").Offset(i - 1, 1)
        Sheets("Sheet2").Range("A1").Offset(k - 1, 2) = Sheets("Sheet1").Range("A1").Offset(0, j)
        Sheets("Sheet2").Range("A1").Offset(k - 1, 3) = Sheets("Sheet1").Range("A1").Offset(i - 1, j - 1)
    Next j
Next i
End Sub