FLATTEN skipping blank cells without using UNIQUE

Question

I'm trying to turn an array into a single column without blank cells, considering that the input will always have some blank cells and that there might be repeated values. I'm trying to use FLATTEN but it keeps the blanks and UNIQUE would kill the repeated values, so I can't use.

I also thought about using something like FLATTEN(QUERY(X:X, "select * WHERE col1,col2,col3,col4,col5 IS NOT NULL") but number of columns might be dynamic so I can't say precisely which columns to use.

My input:

Desired output:

Sample sheet here

Any clue?

Answer 1

Use QUERY on the outside:

=QUERY(FLATTEN(YOUR-RANGE-HERE),"Select * WHERE Col1 Is Not Null")

I left an example in your spreadsheet, cell G2.

UPDATE 2023

One can now use the following new function:

=TOCOL(YOUR-RANGE-HERE, 1)

Answer 2

=flatten(filter(A1:E10;not(isblank(A1:E10))))

or

=flatten(filter(A1:E10;len(A1:E10)))

or

=filter(flatten(A1:E10);len(flatten(A1:E10)))