Forum Discussion

Juha_Krapinoja_Fluido's avatar
Juha_Krapinoja_Fluido
Copper Contributor
Oct 25, 2021
Solved

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 ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    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<>"") ) )

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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<>"") ) )

 

Juha_Krapinoja_Fluido's avatar
Juha_Krapinoja_Fluido
Copper Contributor
Oct 25, 2021
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!

Resources