Home

Combine data from multiple rows into a single row

%3CLINGO-SUB%20id%3D%22lingo-sub-771853%22%20slang%3D%22en-US%22%3ECombine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771853%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20take%20a%20single%20column%20that%20is%20made%20of%20500%20rows%20and%20have%20it%20wrap%20to%2025%20columns%20of%2020%20rows%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-771853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EData%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elayout%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772100%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772100%22%20slang%3D%22en-US%22%3E%3CP%3EWould%20you%20like%20rows%201-20%20of%20the%20old%20data%20to%20be%20in%20column%201%20of%20the%20new%20data%2C%20rows%2021-40%20to%20be%20in%20column%202%2C%20rows%2041-60%20to%20be%20in%20column%203%2C%20etc...%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20then%20do%20the%20following%3A%3C%2FP%3E%3CP%3EAssuming%20all%20of%20your%20data%20is%20in%20the%20range%20A1%3AA500%2C%20put%20this%20formula%20in%20cell%20C1%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24A%241%3A%24A%24500%2CROW(C1)%2B(20*(COLUMNS(%24C%241%3AC%241)-1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copy%20all%20the%20way%20to%20cell%20AA20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mccone
Occasional Visitor

Is there a way to take a single column that is made of 500 rows and have it wrap to 25 columns of 20 rows?

1 Reply

Would you like rows 1-20 of the old data to be in column 1 of the new data, rows 21-40 to be in column 2, rows 41-60 to be in column 3, etc...?

 

If so, then do the following:

Assuming all of your data is in the range A1:A500, put this formula in cell C1:

 

=INDEX($A$1:$A$500,ROW(C1)+(20*(COLUMNS($C$1:C$1)-1)))

 

and copy all the way to cell AA20.

 

Hope this helps!

PReagan