All values of an array to one column or row

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.

excel

Formulas and Functions

SergeiBaklan

Oct 25, 2021

Juha_Krapinoja_Fluido

If 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<>"") ) )

Forum Discussion

All values of an array to one column or row

3 Replies

Resources