SOLVED

Stacking spread out cells in Excel 2019

%3CLINGO-SUB%20id%3D%22lingo-sub-2910415%22%20slang%3D%22en-US%22%3EStacking%20spread%20out%20cells%20in%20Excel%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2910415%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20a%20user%20of%20Excel%20365%20and%20made%20a%20decently%20sized%20Excel%20sheet%20with%20a%20lot%20of%20calculations.%20However%2C%20one%20of%20the%20people%20I%20am%20making%20this%20calculation%20for%20doesn't%20have%20Excel%20365%20but%20owns%20Excel%202019.%20I%20have%20successfully%20converted%20everything%20so%20it's%20compatible%20with%202019%20except%20for%20one%20calculation%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20happen%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lanitheeater_0-1635796241708.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322471i19D273CE7FAC4429%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lanitheeater_0-1635796241708.png%22%20alt%3D%22lanitheeater_0-1635796241708.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20the%20values%20in%20column%20A%20are%20spread%20out%20(and%20this%20is%20variable%2C%20so%20it%20might%20be%20that%20A2%3AA21%20is%20entirely%20filled%20with%20numbers)%20and%20I%20need%20to%20get%20every%20value%20and%20put%20them%2C%20in%20the%20same%20order%2C%20under%20each%20other%20for%20reference%20in%20another%20part%20of%20the%20calculation.%20How%20I've%20done%20it%20in%20365%20is%20by%20adding%20a%20helping%20column%20which%20turns%201%20if%20A%20is%20a%20number%20and%200%20if%20it's%20not%2C%20and%20then%20sort%20the%20values%20in%20the%20column%20A%20by%20helping%20column%20B.%20Not%20a%20very%20clean%20way%20of%20doing%20it%20but%20it%20works%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lanitheeater_1-1635796449338.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322472i559BD019D692D8B8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lanitheeater_1-1635796449338.png%22%20alt%3D%22lanitheeater_1-1635796449338.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20that%20sort%20function%20doesn't%20exist%20prior%20to%20Excel%20365%20and%20I%20can't%20seem%20to%20figure%20out%20a%20way%20of%20doing%20it%20that%20is%20compatible%20with%20Excel%202019.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20this%3F%20Any%20help%20is%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2910415%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%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2910558%22%20slang%3D%22en-US%22%3ERe%3A%20Stacking%20spread%20out%20cells%20in%20Excel%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2910558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202703%22%20target%3D%22_blank%22%3E%40lanitheeater%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%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%20520px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322486iC9164DEE899FF931%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2910846%22%20slang%3D%22en-US%22%3ERe%3A%20Stacking%20spread%20out%20cells%20in%20Excel%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2910846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response.%20It's%20almost%20what%20I%20need%2C%20except%20I%20don't%20want%20to%20sort%20them.%20I%20need%20the%20order%20in%20which%20they%20come.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am a user of Excel 365 and made a decently sized Excel sheet with a lot of calculations. However, one of the people I am making this calculation for doesn't have Excel 365 but owns Excel 2019. I have successfully converted everything so it's compatible with 2019 except for one calculation: 

 

What I need to happen:

lanitheeater_0-1635796241708.png

So the values in column A are spread out (and this is variable, so it might be that A2:A21 is entirely filled with numbers) and I need to get every value and put them, in the same order, under each other for reference in another part of the calculation. How I've done it in 365 is by adding a helping column which turns 1 if A is a number and 0 if it's not, and then sort the values in the column A by helping column B. Not a very clean way of doing it but it works:

lanitheeater_1-1635796449338.png

The problem is that that sort function doesn't exist prior to Excel 365 and I can't seem to figure out a way of doing it that is compatible with Excel 2019.

 

Can someone help me with this? Any help is appreciated!

5 Replies

@lanitheeater 

That could be

image.png

@Sergei Baklan Thank you for your response. It's almost what I need, except I don't want to sort them. I need the order in which they come.

best response confirmed by lanitheeater (New Contributor)
Solution

@lanitheeater 

If like this

image.png

it could be

=IFERROR( INDEX( $A$2:$A$100, AGGREGATE(15, 6, 1/($A$2:$A$100 <> "")*( ROW($A$2:$A$100) - ROW($A$1) ), ( ROW()-ROW($B$1) ) ) ), "")

@Sergei Baklan 

 

Thank you, this is exactly what I needed!

@lanitheeater , you are welcome, glad it helped