Difficult scenerio, move columns to a new line

%3CLINGO-SUB%20id%3D%22lingo-sub-1582306%22%20slang%3D%22en-US%22%3EDifficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582306%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20move%20items%20that%20are%20in%20a%20line%2C%20across%20columns%2C%20to%20be%20moved%20to%20their%20own%20line.%26nbsp%3B%20I%20have%203000%20records%20to%20do%20this%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20lets%20say%20you%20have%20a%20basket%20(column%20A1)%20in%20that%20basket%20is%20cheese%2C%20sausage%2C%20crackers%20and%20wine%2C%20(columns%20b1%2Cc1%2Cd1%2Ce1)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20them%20to%20be%26nbsp%3B%3C%2FP%3E%3CP%3Ea1%20b1%20c1%20d1%20e1%3C%2FP%3E%3CP%3Escreen%20shot%20for%20reference%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20know%20if%20I%20can%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22katphelan_0-1597166549563.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211824iABBFD26C1FA2E7DB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22katphelan_0-1597166549563.png%22%20alt%3D%22katphelan_0-1597166549563.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1582306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582331%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756230%22%20target%3D%22_blank%22%3E%40katphelan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20concrete%20sample%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTRANSPOSE(A1%3AE1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20A4%2C%20but%20I%20guess%20that's%20not%20what%20your%20are%20looking%20for.%20How%20other%2C%20if%20not%202999%20but%209%20records%20looks%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582333%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582333%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756230%22%20target%3D%22_blank%22%3E%40katphelan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20sounds%20like%20you%20are%20looking%20to%20transpose%20your%20array.%20Here%20is%20how%20to%20transpose%20data%3A%3C%2FP%3E%3COL%3E%3CLI%3EHighlight%20A1%3AE1%3C%2FLI%3E%3CLI%3ECopy%20(Ctrl%20%2B%20C)%3C%2FLI%3E%3CLI%3ESelect%20an%20empty%20cell%20that%20has%20four%20additional%20empty%20cells%20below%20it%3C%2FLI%3E%3CLI%3EPaste%20(Ctrl%20%2B%20P)%20then%20press%20Ctrl%20then%20press%20T%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582341%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3Bbut%20how%20do%20i%20do%20this%20for%203000%20lines%20of%20data....do%20i%20have%20to%20put%20in%20empty%20lines%20first%3F%26nbsp%3B%20Its%20easy%20for%20one%20line%20with%204%20columns%20but%20when%20i%20have%203000%20lines%20of%204%20columns...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582352%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756230%22%20target%3D%22_blank%22%3E%40katphelan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%2C%20I%20must%20be%20misunderstanding%20your%20question.%20Does%20transposing%20your%20entire%20array%20not%20yield%20the%20result%20you%20expect%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582358%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756230%22%20target%3D%22_blank%22%3E%40katphelan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20easiest%20way%20to%20combine%204%20columns%20in%20one%20is%20Power%20Query%20if%20you%20consider%20such%20option.%20Otherwise%20with%20INDEX.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582386%22%20slang%3D%22en-US%22%3ERe%3A%20Difficult%20scenerio%2C%20move%20columns%20to%20a%20new%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756230%22%20target%3D%22_blank%22%3E%40katphelan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20536px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211828i0046138C88159382%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(A%3AD%2CINT((ROW()-1)%2F4)%2B1%2CMOD(ROW()-1%2C4)%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have to move items that are in a line, across columns, to be moved to their own line.  I have 3000 records to do this with.

 

Basically lets say you have a basket (column A1) in that basket is cheese, sausage, crackers and wine, (columns b1,c1,d1,e1) 

 

I need them to be 

a1 b1 c1 d1 e1

screen shot for reference

 

Anyone know if I can do this?

 

katphelan_0-1597166549563.png

 

6 Replies
Highlighted

@katphelan 

For this concrete sample you may use

=TRANSPOSE(A1:E1)

in A4, but I guess that's not what your are looking for. How other, if not 2999 but 9 records looks like?

Highlighted

Hello @katphelan,

 

It sounds like you are looking to transpose your array. Here is how to transpose data:

  1. Highlight A1:E1
  2. Copy (Ctrl + C)
  3. Select an empty cell that has four additional empty cells below it
  4. Paste (Ctrl + P) then press Ctrl then press T

 

Highlighted

@PReagan but how do i do this for 3000 lines of data....do i have to put in empty lines first?  Its easy for one line with 4 columns but when i have 3000 lines of 4 columns...

Highlighted

@katphelan,

 

I'm sorry, I must be misunderstanding your question. Does transposing your entire array not yield the result you expect?

Highlighted

@katphelan 

The easiest way to combine 4 columns in one is Power Query if you consider such option. Otherwise with INDEX.

Highlighted

@katphelan 

Like this

image.png

with

=INDEX(A:D,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)