Query: select cells in column where it is not null

Question

I am trying to pull non null data from a column. I have tried the following.

Each import range works independently.

=query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34");IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34")}, "select Col1 where Col1 <> '' ")


=query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34");IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34")}, "select A where B is not null")

Answer 1

There are two alternatives that you might consider: FILTER and QUERY

Here's a link to a spreadsheet

FILTER

=filter(A4:A34,not(isblank(A4:A34)))

It doesn't matter whether the data is text, numeric or a combination.

QUERY
If the data is text - =QUERY(A4:A34, "select A Where A <>'' ")
If the data is numeric - =QUERY(A4:A34, "select A Where A is not NULL ")

There are two drawbacks to QUERY.

First, if your data consists of both numbers and text then it doesn't work.
Second, should you move the target cell, you have to edit the QUERY. The data range will be updated, but the Select statement will be unchanged and will need to be edited.

This image shows the various results. The red box highlights where QUERY can't handle the combination of text and/or numeric data.

---