SOLVED

All values of an array to one column or row

Copper Contributor

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.


3 Replies
best response confirmed by Juha_Krapinoja_Fluido (Copper Contributor)
Solution

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

 

Wow, Sergei! The perfect answer in just 24 minutes!
That really seems to be EXACTLY, what I was looking for! Couldn't really be any better!

@Juha_Krapinoja_Fluido , glad it helped.

That's existing pattern.

1 best response

Accepted Solutions
best response confirmed by Juha_Krapinoja_Fluido (Copper Contributor)
Solution

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

 

View solution in original post