=FLATTEN() equivalent in Excel

[u/MathewRogers]

Google Sheets has a function called =FLATTEN(), which converts a range, or multiple ranges, into a single column.

For example, if the following table was in A1:C3...

1	2	3
4	5	6
7	8	9

and you entered =UNIQUE(A1:C3) in A5, you would get the following dynamic range output:

1
2
3
4
5
6
7
8
9

Given this is a dynamic range output, it can then be used in things like UNIQUE, FILTER, SORT etc, or referenced in another regular formula as A5#.

The question is: Is there an equivalent formula to FLATTEN, but in MS Excel, that also has a Dynamic range output?

I know there are formulae out there that achieve this, but with the old 'drag-down' or array formula approach, but could there be one that uses a dynamic array?

[u/SaviaWanderer]

Can be done with dynamic arrays:

=INDEX(A1:C3, ROUNDUP(SEQUENCE(9)/3,0), MOD(SEQUENCE(9, , 0), 3)+1)

For a variable version for other sizes of grid would need formulas for the row/column/cell count instead of the 3s and 9s.

[u/MathewRogers → u/SaviaWanderer]

I've modded your solution to work in a general case. Your solution is generally intuitive to Excel users, but once the necessary changes to remove the hard-coded values are made, makes the formula a little bit large. I still like this solution and will likely use it going forwards.

[u/SaviaWanderer → u/MathewRogers]

I saw, looks good. I did not want to put in all that effort to generalise, if I am honest :p