transpose columns to rows

drtopserv
drtopserv used Ask the Experts™
on
Hi,
I would like to do this programatically :
I have this sheet1:

ID  CODE1   CODE2  
1        111         222      
2        666          67        
3         321       234    

I would like to run code/function/macro whatever to be able to have the result:

ID  
1   CODE1  111
1   CODE2  222
2   CODE1  666
2   CODE2  67
3   CODE1  321
3   CODE2  234
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you can easily do this with free Add-in Power Query

see the tutorial
GrahamSkanRetired
Top Expert 2012

Commented:
Have you tried Paste>Transpose?

Author

Commented:
appretiate another solution becuase i can`t install power-query at this time , my computer don`t have explorer 9 .
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Paste>Transpose? will do :
ID  CODE1   CODE2  
1        111         222      
 2        666          67        
 3         321       234    

ID           1        2          3
CODE1  111    666      321
CODE2   222   67         234
Microsoft Excel Expert
Top Expert 2014
Commented:
then you can use the attached VBA solution.

run the macro "CallUnPivotBySQL" then follow
Call-UnPivot.xlsm
Top Expert 2015
Commented:
Use this code...

Sub arrangedata()
    Dim rng As Range, cell As Range
    Dim lrow As Long, lcol As Long
    Dim ws As Worksheet, ws1 As Worksheet
    Dim k As Long, lr1 As Long

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    lcol = ws.Cells(1, Cells.Columns.Count).End(xlToLeft).Column

    Set rng = ws.Range("A2:a" & lrow)
    ws1.Cells.Clear
    ws.Range("A1").Copy ws1.Range("A1")

    For Each cell In rng
        k = 2
        Do Until k > lcol
            lr1 = ws1.Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1
            ws1.Cells(lr1, "a").Value = cell.Value
            ws1.Cells(lr1, "B").Value = ws.Cells(1, k).Value
            ws1.Cells(lr1, "C").Value = ws.Cells(cell.Row, k).Value
            k = k + 1
        Loop


    Next cell

End Sub

Open in new window


This will move data from sheet1 to sheet2 in the desired format..Enclosed is the workbook for your reference..

Saurabh...
ArrData.xlsm
Without code

If your given data starts from A1 on sheet1 then use this formula on A2 Sheet2 and copy it down and across

=INDEX(Sheet1!$1:$1048576,IF(COLUMN()=2,1,(ROW()+2)/2),IF(COLUMN()=1,1,IF(MOD(ROW(),2),3,2)))

Author

Commented:
BEST OF THE BEST.
THANKS ALOTTT
WORKS AS A CHARM!
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome. i am glad i was able to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial