SOLVED

New Contributor

# Stacking spread out cells in Excel 2019

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:

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:

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

That could be

# Re: Stacking spread out cells in Excel 2019

@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

# Re: Stacking spread out cells in Excel 2019

If like this

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) ) ) ), "")``

# Re: Stacking spread out cells in Excel 2019

Thank you, this is exactly what I needed!

# Re: Stacking spread out cells in Excel 2019

@lanitheeater , you are welcome, glad it helped