Transpose the rows into columns

Question

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 15-Dec-15 3:35am

tastini

Add a Solution

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?

tastini 15-Dec-15 10:02am

not for one time use , doing it each 4days or something.

CHill60 15-Dec-15 10:03am

So you will have some VBA code behind a button or something ... what is the code that you tried?

tastini 15-Dec-15 10:05am

really sorry , i never use vba code with excel. there is no other option that i can apply it will be really helpful.

CHill60 15-Dec-15 10:44am

I can't think of any way that this could be achieved using Transpose and Pivot without including the use of Macro.
This is a programming site after all :laugh:

tastini 16-Dec-15 2:01am

i agree you are 100% correct. i use only excel but not macro not all the people knows macros and if we dont know macro we cant ask doubts?, somebody can help me from here to solve this issue, even i have no idea about coding in excel. I am sorry if i am in wrong place.

[no name] 15-Dec-15 13:04pm

In Excel... you mean directly in Excel, not by "Excel OLE Automation"?
Anyway for both you can copy the required area to Clipboard and "paste Content" for which you can choose the Option "transpose".

For further help, the Excel Version you are using would be helpfull.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Rajdeep Debnath · Accepted Answer · 2015-12-15T04:40:00

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