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

 

[alexwangsa]

Yes, I created a blank sheet 'Sheet2'. THe highlighted range was from B6:J17

 

[jindon]

Oops
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 ii = 2 To UBound(a,2)
    For i = 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]

Hi, you managed to exclude all the blank cells in the output. I have change the code in line 13. Range ("hh7") to ("A1") because that's where I want the output to start.

Almost there.

The issue is : first column of output contains description from A7 instead of B7 in the source.

 

[jindon]

change

a = Range("b6").CurrentRegion.Value

to

With Range("b6").CurrentRegion
    a = .Resize(, .Columns.Count - 1).Offset(, 1).Value
End With

 

[alexwangsa]

PERFECT ! Thanks Jindon.

 

[jindon]

You are welcome.

However, when I saw your sheet thought the link, I thought there was nothing in col.A, so that I just used Range("b6").CurrentRegion.

But the fact is you have something in Col.A.

I don't understand why you started from col.B, but it is always better start from Col.A for the later manipulation.

 

[alexwangsa]

I delete the contents of column A which is product description, because I only need product codes. Once I get the output, I am going to put them into microsoft access.