Search with query formula where cells are not nothing

Question

I have range of cells and want to search with query formula where cells are not nothing.

    A   B   C   D
1   a   11  44  qw
2   b   12  r   
3   c   13  44  444
4   NOT         
5   f   15  55  88
6   NOT         
7   h   17  gh  ee

Cells in C maybe any number, maybe any word, maybe any nothing.

I want to select everything A where C has nothing. Result must be:

a
b
c
f
h

When try all the querys formula:

=QUERY(A1:D7,"select A where not C<>'' ")
=QUERY(A1:D7,"select A where not C!='' ")
=QUERY(A1:D7,"select A where not C is null")
=QUERY(A1:D7,"select A where C !='' or not C is null")

Nothing give correct result. Looked everywhere.

What can I do?

Related: https://stackoverflow.com/questions/68562518/query-is-ignoring-string-non-numeric-value — TheMaster, Oct 05 '22 at 21:58

score 2 · Accepted Answer · answered May 01 '20 at 09:20

2

Google QUERY function does not work here as "expected" because of mixed data types in column C. If we apply the formula =QUERY(A1:C7;"select A where C is not null";-1) it will return only "a, c and f". The image below shows this result in column G, where G1 contains the above formula.

We should check data types more carefully, but it is difficult to do inside QUERY function. So I suggest to add one more column (E in our case) and fill it with "=TYPE(C1)" and similar functions. Having data types column, we can modify QUERY expression to take them into account. Final result is shown in column H.

Please check this approach again, if you are going to use other data types in column C.

answered May 01 '20 at 09:20

Александр Ермолин

1,145
1
10
17

Nice workaround. But **what happens when the sheet is linked to a form and new answers arrive**? How do you treat that? How can you automatically fill-in column `E` with a rule without manually adding `=TYPE(C1)` and so on? – marikamitsos May 01 '20 at 10:42
Of course, in some cases there is no possibility to add column. Author says nothing about such requirements. As for formula copying, there is ARRAYFORMULA alternative. For example, this column would be useful: =ARRAYFORMULA(ISBLANK(C1:C)) to my mind. – Александр Ермолин May 01 '20 at 12:41

score 0 · Answer 2 · answered May 01 '20 at 01:34

0

=QUERY(A1:D7, "select A where C is not null")

...is what you need

answered May 01 '20 at 01:34

player0

124,011
12
67
124

Search with query formula where cells are not nothing

2 Answers2