Transpose from multiple column

[Vishaal]

Hi, we want to transpose some data from multiple column, is there any option to do it

Query Table

Code											Amount											Qty
1	2	3	4	5	6	7	8	9	10		1	2	3	4	5	6	7	8	9	10		1	2	3	4	5	6	7	8	9	10
807	808	824	793	812							109	113	165	266	101							1	1	2	1	1
808											113											1
SD-DE-16	853	808									125	90	113									2	4	2
866											165											1

Result Table

Code	Qty	Amount
807	1	109
808	1	113
824	2	165
793	1	266
812	1	101
808	1	113
SD-DE-16	2	125
853	4	90
808	2	113
866	1	165

 

[rpaulson]

is VBA and option?

 

[Vishaal]

is VBA and option?

yes

 

[rpaulson]

try this on a copy of your file.
this will copy the data out into columns BA: thru BC

Sub Do_it()

wr = 2
For r = 3 To Cells(Rows.Count, "A").End(xlUp).Row
    For c = 1 To 10
    
    code = Cells(r, c)
        If code = "" Then GoTo 222 'last code reach jump to next row
    amt = Cells(r, c + 11)
    qty = Cells(r, c + 22)
   
    Cells(wr, "BA") = code
    Cells(wr, "BB") = qty
    Cells(wr, "BC") = amt
    
    wr = wr + 1
    
    Next c
222 Next r

End Sub

hth,
Ross

 

[Vishaal]

Thanks rpaulson