How can I combine rows of varying length like above?

I want a function, that can combine rows 1-4 and end up with one row with all the values like row 6

CodePudding user response：

use:

=TRANSPOSE(QUERY(FLATTEN(A1:C4); "where Col1 is not null"; 0))

CodePudding user response：

QUERY() function is best for this case. Alternatively you can use FILTER() formula like-

=TRANSPOSE(FILTER(FLATTEN(A1:C4),FLATTEN(A1:C4)<>""))