Nov 01 2021 01:05 PM
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!
Nov 01 2021 02:34 PM
@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.
Nov 01 2021 02:57 PM
SolutionIf 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) ) ) ), "")
Nov 03 2021 08:51 AM
Nov 03 2021 01:23 PM
@lanitheeater , you are welcome, glad it helped
Nov 01 2021 02:57 PM
SolutionIf 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) ) ) ), "")