Need help with excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2428173%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428173%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20flip%20rows%20on%20a%20same%20line%20if%20ID%20of%20rows%20the%20same%2C%20can't%20get%20it%20to%20work%2C%20can%20you%20guys%20please%20help%3C%2FP%3E%3CP%3EHere's%20an%20exemple%20of%20what%20i'm%20trying%20to%20do%26nbsp%3B%3C%2FP%3E%3CP%3EID1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Product1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount1%3C%2FP%3E%3CP%3EID1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Product2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount2%3C%2FP%3E%3CP%3EID1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Product3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20to%20have%20this%20way%3C%2FP%3E%3CP%3EID1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProduct1%26nbsp%3B%20%26nbsp%3B%20Amount%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProduct2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProduct3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Amount3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2428173%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2428932%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074063%22%20target%3D%22_blank%22%3E%40Magic115%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20this%20that%20should%20help%20you%3A%20%3CA%20href%3D%22https%3A%2F%2Ftrumpexcel.com%2Finsert-blank-row-after-every-row%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftrumpexcel.com%2Finsert-blank-row-after-every-row%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20get%20a%20blank%20in-between%20each%20row%2C%20try%20using%20using%20the%20special%20paste%20%22Transpose%22%20to%20list%20out%20your%20data%20horizontally.%20Do%20the%20same%20with%20the%20third%20column%20and%20you%20should%20be%20able%20to%20almost%20interlace%20the%20two%20using%20paste%20special%20combined%20with%20the%20%22Skip%20blanks%22%20paste%20special%20feature.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2429518%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2429518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074063%22%20target%3D%22_blank%22%3E%40Magic115%3C%2FA%3E%26nbsp%3BSince%20you%20tagged%20you%20post%20with%20Office365%2C%20I%20assume%20you%20can%20use%20the%20new%20UNIQUE%2C%20TEXTJOIN%20%26nbsp%3Band%20FILTER%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20this%20is%20a%20on-time%20exercise%20only%2C%20consider%20a%20simple%20three-step%20process%20to%20rearrange%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%20create%20a%20list%20of%20unique%20ID's%20(assuming%20your%20list%20contains%20many%20like%20in%20your%20example)%3C%2FP%3E%3CP%3EStep%202%3A%20filter%20all%20records%20that%20match%20an%20ID%20and%20join%20it%20all%20together%20in%20one%20comma%20separated%20text%20string%3C%2FP%3E%3CP%3EStep%203%3A%20copy%20the%20result%20from%20step%202%20and%20paste%20as%20values.%20Then%20use%20Text-to-columns%20from%20the%20Data%20ribbon%20to%20split%20the%20text%20into%20separate%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20are%20finished%2C%20delete%20the%20columns%20that%20contain%20steps%201%20and%202%20to%20end%20up%20with%20a%20list%20of%20unique%20ID's%20and%20associated%20products%20and%20amounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20if%20you%20need%20something%20more%20sophisticated%2C%20please%20come%20back%20with%20a%20realistic%20sample%20of%20your%20data%2C%20omitting%20any%20private%20and%20confidential%20information%20of%20course.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

I'm trying to flip rows on a same line if ID of rows the same, can't get it to work, can you guys please help

Here's an exemple of what i'm trying to do 

ID1      Product1      Amount1

ID1      Product2      Amount2

ID1      Product3      Amount3

 

Need to have this way

ID1     Product1    Amount 1     Product2      Amount2       Product3      Amount3

 

Thank you

2 Replies

@Magic115 

 

I found this that should help you: https://trumpexcel.com/insert-blank-row-after-every-row/

 

Once you get a blank in-between each row, try using using the special paste "Transpose" to list out your data horizontally. Do the same with the third column and you should be able to almost interlace the two using paste special combined with the "Skip blanks" paste special feature.

@Magic115 Since you tagged you post with Office365, I assume you can use the new UNIQUE, TEXTJOIN  and FILTER functions.

 

And if this is a on-time exercise only, consider a simple three-step process to rearrange the data.

 

Step 1: create a list of unique ID's (assuming your list contains many like in your example)

Step 2: filter all records that match an ID and join it all together in one comma separated text string

Step 3: copy the result from step 2 and paste as values. Then use Text-to-columns from the Data ribbon to split the text into separate columns.

 

Once you are finished, delete the columns that contain steps 1 and 2 to end up with a list of unique ID's and associated products and amounts.

 

Now, if you need something more sophisticated, please come back with a realistic sample of your data, omitting any private and confidential information of course.