Oct 25 2021 09:56 AM
I'm looking for a dynamic array formula, which I can use in order to list all values of the cells e.g. in the area "$B$2:$Q$350" as a list in one single column (or row).
So far, I have only found such solutions, where I would have to copy a normal formula across several rows. That solution doesn't however work, if the number of values increases. Hence, I would prefer a dynamic array formula.
**** Implementation suggestion ****
If this funtion doesn’t exist yet, it would be quite nicely implementable by adding an optional parameter in the TRANSPOSE function:
TRANSPOSE(array, [direction])
Where "direction" would indicate, whether the values would be collected:
0 = by column
1 = by row
Other operations (e.g. skipping empty cells, finding unique values) could be done with the existing formulas, should they not be added as additional optional parameters to the TRANSPOSE function itself.
Oct 25 2021 10:20 AM
SolutionIf don't touch functions which are only in Beta now, mock-up could be
=LET(
data, $B2:Q350,
rs, ROWS(data),
cls, COLUMNS(data),
k, SEQUENCE(rs*cls),
v, INDEX(data, MOD(k-1,rs)+1, INT((k-1)/rs)+1 ),
UNIQUE( FILTER(v, v<>"") ) )
Oct 25 2021 10:32 AM
Oct 25 2021 10:38 AM
@Juha_Krapinoja_Fluido , glad it helped.
That's existing pattern.
Oct 25 2021 10:20 AM
SolutionIf don't touch functions which are only in Beta now, mock-up could be
=LET(
data, $B2:Q350,
rs, ROWS(data),
cls, COLUMNS(data),
k, SEQUENCE(rs*cls),
v, INDEX(data, MOD(k-1,rs)+1, INT((k-1)/rs)+1 ),
UNIQUE( FILTER(v, v<>"") ) )