Hi,

I'm using Excel 2010, and I'm not extremely advanced when it comes to the macro side of Excel.  However, What I want to do is to transpose multiple rows to columns . below is the example for better understanding of the query . Here is an example of the current data:  

ITEM	DSA	SUL	SBM	SAN	RIF	RAM	ISM	BCC	ENM	220
253061161001	4	2	4	2	2	2	2	0	2	2
253061161002	4	2	4	2	2	2	2	0	2	2
253061161003	6	2	4	2	2	2	2	0	2	2
253061161004	8	3	6	3	3	3	3	0	3	3
253061161005	6	3	6	3	3	3	3	0	3	3
253061162001	4	4	4	2	2	2	2	0	2	2
253061162002	4	4	4	2	2	2	2	0	2	2
253061162003	5	4	4	2	2	2	2	0	2	2
253061162004	7	6	6	3	3	3	3	0	3	3
253061162005	7	6	6	3	3	3	3	0	3	3

this need to be converted in three columns , item row is repetitive .  column and rows is not fixed . so i need macro as long the data is , in below format . 

ITEM	loc	qty
253061161001	DSA	4
253061161002	DSA	4
253061161003	DSA	6
253061161004	DSA	8
253061161005	DSA	6
253061162001	DSA	4
253061162002	DSA	4
253061162003	DSA	5
253061162004	DSA	7
253061162005	DSA	7
253061161001	SUL	2
253061161002	SUL	2
253061161003	SUL	2
253061161004	SUL	3
253061161005	SUL	3
253061162001	SUL	4
253061162002	SUL	4
253061162003	SUL	4
253061162004	SUL	6
253061162005	SUL	6
253061161001	SBM	4
253061161002	SBM	4
253061161003	SBM	4
253061161004	SBM	6
253061161005	SBM	6
253061162001	SBM	4
253061162002	SBM	4
253061162003	SBM	4
253061162004	SBM	6
253061162005	SBM	6
253061161001	SAN	2
253061161002	SAN	2
253061161003	SAN	2
253061161004	SAN	3
253061161005	SAN	3
253061162001	SAN	2
253061162002	SAN	2
253061162003	SAN	2
253061162004	SAN	3
253061162005	SAN	3
253061161001	RIF	2
253061161002	RIF	2
253061161003	RIF	2
253061161004	RIF	3
253061161005	RIF	3
253061162001	RIF	2
253061162002	RIF	2
253061162003	RIF	2
253061162004	RIF	3
253061162005	RIF	3
253061161001	RAM	2
253061161002	RAM	2
253061161003	RAM	2
253061161004	RAM	3
253061161005	RAM	3
253061162001	RAM	2
253061162002	RAM	2
253061162003	RAM	2
253061162004	RAM	3
253061162005	RAM	3
253061161001	ISM	2
253061161002	ISM	2
253061161003	ISM	2
253061161004	ISM	3
253061161005	ISM	3
253061162001	ISM	2
253061162002	ISM	2
253061162003	ISM	2
253061162004	ISM	3
253061162005	ISM	3
253061161001	BCC	0
253061161002	BCC	0
253061161003	BCC	0
253061161004	BCC	0
253061161005	BCC	0
253061162001	BCC	0
253061162002	BCC	0
253061162003	BCC	0
253061162004	BCC	0
253061162005	BCC	0

I realize I'm probably going to have to create some sort of macro that can loop through each of these columns.  I'm not exactly sure how to do this or how to get started, but I was hoping someone would be able to provide me with a good starting point to accomplish this.  Thanks in advance! Please Help

• Edited by Roderix 20 hours 33 minutes ago Excel help

Here is such a macro:

Sub Transform()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set wshS = ActiveSheet
    Set wshT = Worksheets.Add(After:=wshS)
    wshT.Cells(1, 1).Value = "Item"
    wshT.Cells(1, 2).Value = "Loc"
    wshT.Cells(1, 3).Value = "Qty"
    t = 1
    m = wshS.Cells(wshS.Rows.Count, 1).End(xlUp).Row
    n = wshS.Cells(1, wshS.Columns.Count).End(xlToLeft).Column
    For c = 2 To n
        For r = 2 To m
            t = t + 1
            wshT.Cells(t, 1).Value = wshS.Cells(r, 1).Value
            wshT.Cells(t, 2).Value = wshS.Cells(1, c).Value
            wshT.Cells(t, 3).Value = wshS.Cells(r, c).Value
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub