FLATTEN skipping blank cells without using UNIQUE

user_5606352 0
Viewed 2786

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:

enter image description here

Desired output:

enter image description here

Sample sheet here

Any clue?

google-sheets-formula google-sheets
2 Answers

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.

user_139032830 
=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)))
user_94717610
Related