Click here to Skip to main content
15,881,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am trying to transpose the rows data to columns in excel. please check the example down
i have the data like this

OrderNo Customer Mobile Item Item Des Price
1 abc 000 xyz xyz item 90
1 abc 000 abc abc item 80
2 xyz 111 abc abc item 80
3 pqr 222 xyz xyz item 90
3 pqr 222 abc abc item 80
3 pqr 222 pqr pqr item 70

i want to make it like below

OrderNo Customer Mobile Item ItemDesc Price Item2 ItemDesc2 Price2 Item3 ItemDesc3 Price
1 abc 000 xyz xyz item 90 abc abc item 80
2 xyz 111 abc abc item 90
3 pqr 222 xyz xyz item 90 abc abc item 80 pqr pqr item 70
--------------------------------------------------------------------------------------
i have 560 rows,maximum duplication is 5 rows and i tried transpose function in excel but no luck its not given me the result what i want to. please help me to solve this issue.
Posted
Comments
CHill60 15-Dec-15 9:51am    
What code did you use to transpose the data?
tastini 15-Dec-15 10:04am    
i am using excel ,i am sorry i dont understand what you meant by code?
ZurdoDev 15-Dec-15 9:51am    
You could write code to do it but with only 560 rows I think you'd be faster to do it manually.
Rajdeep Debnath 15-Dec-15 10:01am    
you can use macro.
tastini 15-Dec-15 10:03am    
can you help me , how i can do it with macro?

1 solution

Please try the below....this is working....


VB.NET
Sub Macro1()
    Dim prevRow As Integer
    Dim currentRow As Integer
    Dim strTemp As String
    Dim pasteColumnIdx As Integer
    
    prevRow = 2 '1st row is for column heading
    currentRow = prevRow + 1 'starts from row 3
    pasteColumnIdx = 8
    
    Do While Cells(currentRow, 1).Text <> ""
    
        If Cells(prevRow, 1).Text = Cells(currentRow, 1).Text Then
            Range(Cells(currentRow, 4), Cells(currentRow, 7)).Select
            Selection.Cut
            Range(Cells(prevRow, pasteColumnIdx), Cells(prevRow, pasteColumnIdx)).Select
            pasteColumnIdx = pasteColumnIdx + 4
            ActiveSheet.Paste
            strTemp = CStr(currentRow) + ":" + CStr(currentRow)
            Rows(strTemp).Select
            Selection.Delete Shift:=xlUp
            'currentRow = currentRow + 1
        Else
            prevRow = prevRow + 1
            currentRow = currentRow + 1
            pasteColumnIdx = 8
        End If
    Loop
    
End Sub
 
Share this answer
 
Comments
tastini 16-Dec-15 1:58am    
I try to run this code... i can see it is working but dont know how to use it properly. Really thanks for this help. i will try to figure it out how to run in my scenario.
CHill60 16-Dec-15 4:37am    
If you locate the "Visual Basic" section of your Excel menu (in 2010 this is via the Developer tab on the ribbon) you will get a new window with various sections. On the tree-structure on the left double-click on "ThisWorkBook" - you will get a blank (white usually) module in the large pane on the right.
Paste the code into that pane.
To run it click anywhere in the window where the code is and hit the F5 key.
To don't actually have to put the macro behind a button, you can just do this F5 trick each time
Rajdeep Debnath 16-Dec-15 2:26am    
You welcome. Please let me know, if you are facing further issue.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900