google sheets - ArrayFormula expand to a whole column

Question

I have this formula that splits 7 columns into 84 columns.

=arrayformula(SPLIT(arrayformula(join(";",rept(B2:H2&";",12))),";"))

I'd like to use the arrayformula but for the entire rows without having to copy the formula.

I tried the following but it doesn't work: =arrayformula(SPLIT(arrayformula(join(";",rept(B2:B:H2:H&";",12))),";"))

https://docs.google.com/spreadsheets/d/1qtl_HVMHelT59-ysk8q0-CzuxOt9RWLbYe0ANUW9G6U/edit?usp=sharing

Thank you!

question from:https://stackoverflow.com/questions/65889869/arrayformula-expand-to-a-whole-column

Answer 1

JOIN() only accepts a 1 dimensional array argument so we need to use other functions to 'join' the entries:

You can use:

=ArrayFormula(split(transpose(query(transpose(rept(B2:H8&";",12)),,50000)),";"))

Sample Sheet:

References:

Concatenate 2D array to single column

TRANSPOSE()

QUERY()