"Broken spacing" google spreadsheets and QUERY function?

Question

Google Sheets

Good day.
Please help me make a selection from the broken interval.
It is necessary to return the values ​​of the A:A column corresponding to the null values ​​of the B:B column, except for those in the P:P column.
something like

unique(query(A:B;"select A where B='' and A!="query(P:P;"select P")))

Naturally, this formula does not work, please help me figure it out!

Answer 1

I understand that the author has most likely already solved his problem, but suddenly it will come in handy for someone.
In your case, there is no need to make the interval broken since it is on the same table. Therefore, we simply expand the search area from A:B to A:P.
=query(A:P;"select A where (B is null and A!=P)")
If you still need to use a broken range, if you need to glue data from different tables, then you need to use curly brackets {} for gluing, ";" for gluing ranges vertically, "," - for gluing ranges horizontally. However, in some cases "," does not work, probably for all Cyrillic users, in which case "\" must be used. If ";" does not work , then "," must be used instead.
In your case it will look like this:

=query({A:B,P};"select Col1 where (Col2 is null and Col1!=Col3)")

or in case of an error, like this:

=query({A:B\P};"select Col1 where (Col2 is null and Col1!=Col3)")

Useful links:
https://spreadsheet.dev/range-in-google-sheets
https://gist.github.com/contributorpw/70759205b0f2...
https://infoinspired.com/google-docs/spreadsheet/h. ..