4

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?

aabujamra
  • 4,494
  • 13
  • 51
  • 101

2 Answers2

10

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)

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
1
=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)))
Ogi22
  • 37
  • 8