Convert from table to a list

[alexwangsa]

Hi,

I need to convert this kind of table to a list (like in below example)
Can someone give me an instruction or macro to do this ?

Table
X Y Z
A 3 5 7
B 2 4 6
C 8 9 10

List
Column1 Column2 Column3
A X 3
A Y 5
A Z 7
B X 2
B Y 4
B Z 6
C X 8
C Y 9
C Z 10

Thanks in advance,
Alex

 

[onlyadrafter]

Hello,

does this work as expected?

Sub create_table()
    For MY_ROWS = 2 To Range("A65536").End(xlUp).Row
        For MY_COLS = 2 To Range("A2").End(xlToRight).Column
            Range("F65536").End(xlUp).Offset(1, 0).Value = Range("A" & MY_ROWS).Value
            Range("G65536").End(xlUp).Offset(1, 0).Value = Cells(1, MY_COLS).Value
            Range("H65536").End(xlUp).Offset(1, 0).Value = Cells(MY_ROWS, MY_COLS).Value
        Next MY_COLS
    Next MY_ROWS
End Sub

I have assumed your data for A is in A2 and X is in B1.

 

[alexwangsa]

It works. But I need some adjustments :
1. Can we have the result on a new blank worksheet ?
2. The column heading starts from C6 to FB6 sometimes up to FG6 so it varies. And so is the row heading, it starts from B7 and varies too. So we need to count the number of rows and columns.
3. The data always starts from C7, same length as the headings. Some of the data cells are blanks

Hope this is clear.

Cheers,
Alex

 

[onlyadrafter]

Hello,

is this it?

Sub create_table()
    For MY_ROWS = 7 To Range("B65536").End(xlUp).Row
        For MY_COLS = 3 To Range("C7").End(xlToRight).Column
            Sheets("Sheet2").Range("F65536").End(xlUp).Offset(1, 0).Value = Range("B" & MY_ROWS).Value
            Sheets("Sheet2").Range("G65536").End(xlUp).Offset(1, 0).Value = Cells(6, MY_COLS).Value
            Sheets("Sheet2").Range("H65536").End(xlUp).Offset(1, 0).Value = Cells(MY_ROWS, MY_COLS).Value
        Next MY_COLS
    Next MY_ROWS
End Sub

 

[alexwangsa]

It only takes data from the first 3 columns. Any idea ?

 

[onlyadrafter]

Hello,

don't quite understand.

To test my code i created the following

C6 to H6 contains a, b, c, d, e, f.

B7 to B11 contains z, y, x, w, v.

C7 to H7 all contain 1
C8 to H8 all contain 2
C9 to H9 all contain 3
C10 to H10 all contain 4
C11 to H11 all contain 5

and the result I get on sheet 2 is

z a 1
z b 1
z c 1
z d 1
z e 1
z f 1
y a 2
y b 2
y c 2
y d 2
y e 2
y f 2
x a 3
x b 3
x c 3
x d 3
x e 3
x f 3
w a 4
w b 4
w c 4
w d 4
w e 4
w f 4
v a 5
v b 5
v c 5
v d 5
v e 5
v f 5

what results are you expecting?

 

[alexwangsa]

That is exactly the result I want.

I made tiny adjustment to the code and it works now. I change C7 (line 3) to HH7 and now I get full results.

The problem is it doesn't give me the right data (1, 2, 3 etc) because in my source I have some blank cells
(http://tinypic.com/view.php?pic=2qusolf&s=4)

Could you please fix the code to give me the right data ?

Cheers

 

[jindon]

try

Sub test()
Dim a, b(), i As Long, ii As Long, n As Long
a = Range("b6").CurrentRegion.Value
ReDim b(1 To UBound(a,1) * UBound(a,2), 1 To 3)
For i = 2 To UBound(a,2)
    For ii = 2 To UBound(a,1)
        If a(i, ii) <> "" Then
            n = n + 1
            b(n, 1) = a(i,1) : b(n, 2) = a(1, ii) : b(n, 3) = a(i,ii)
        End If
    Next
Next
Sheets("sheet2").Range("hh7").Resize(n,3).Value = b
End Sub

 

[alexwangsa]

Thanks. But, when I run this script, I get 'subscript out of range' error message.

 

[jindon]

Do you have sheet named "Sheet2" ?
or which line was hilited when you debug ?