0

I want to return results for col A & B where B is not blank and where F doesn't contain any city in California so, ', CA'. I can get each operator to work separately,

=QUERY('805'!A1:F1105, "select A, B where not F contains', CA'",1) - Works

=QUERY('805'!A1:F1105, "select A, B where B is not null",1) - Also works

but when put together, it does not work. I'm thinking maybe a syntax error?

=QUERY('805'!A1:F1105, "select A, B where not F contains', CA' or where B is not null",1) -Does not work

My spreadsheet - the formula is in cell B10 of Reconciliation tab

Improve this question
5
  • Your post explains things as if we already know what your sheet contains, how it's set up, what data types and ranges there are, etc. But we can't see what you see, nor do we know what you know. So even reading the first sentence of your post, there is no reference point for those of us "out here." I recommend that you share a link to your sheet (or to a copy of it), being sure to set the link's Share permissions (when you create the link) to "Anyone with the link..." and "Editor," which will allow full access to test and leave possible solutions for you.
    – Erik Tyler
    Commented Sep 26, 2021 at 21:03
  • docs.google.com/spreadsheets/d/…
    – southern_exposure
    Commented Sep 26, 2021 at 21:12
  • Formula is in cell B10 of Reconciliation tab
    – southern_exposure
    Commented Sep 26, 2021 at 21:12
  • 1
    =QUERY('805'!A1:F1105, "select A, B where not F contains', CA' or B is not null",1)
    – Boris Baublys
    Commented Sep 27, 2021 at 0:35
  • In other words, you don't need the second where.
    – Tedinoz
    Commented Sep 27, 2021 at 1:05

1 Answer 1

Reset to default
1

It looks to me like you just need to change your or to an and:

=QUERY('805'!A:F, "select A, B where not F contains ', CA' AND B is not null",1)

Improve this answer
2
  • Thanks Erik - I can't believe it was that simple. That worked. And thanks for looking out on the Notes column. That will be removed. I'm repurposing this sheet to maintain color and formatting but most of the columns will not be utilized.
    – southern_exposure
    Commented Sep 27, 2021 at 2:11
  • Great. I'm glad to know that it worked and that there was no need for the additional concern. (I always hate seeing people invest time into something I know from the outset is doomed to fail or cause frustration for them down the road.)
    – Erik Tyler
    Commented Sep 27, 2021 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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