1

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")
0

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.


enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.