Query to list data that's not listed in another column

Question

=QUERY({startingdates!D2:D, Cancelledtours!D2:D},"Select Col1 where not(Col1) matches Col2")

This seems to work for Column A but not column D. I'm at a dead-end. Can anyone help?

The reason that you are having problems with Column D is... it is the only one with any Cancelled Dates. — Tedinoz, Dec 07 '19 at 10:58
your formula is just wrong and it works for A column just by coincidence — player0, Dec 07 '19 at 11:30
I added the first tour date in col A and it worked, maybe I was mistaken though. Not at the computer now. — user198561, Dec 07 '19 at 16:13

Tom Sharpe · Accepted Answer · 2019-12-07T12:26:18.587

1

You have to do a lookup on the starting date - the query as it stands only looks in the same row to see if there is a match:

=ArrayFormula(QUERY({StartingDates!D2:D, isnumber(vlookup(StartingDates!D2:D,CancelledTours!D2:D,1,false))},"Select Col1 where Col2=false"))

or slightly shorter using match:

=ArrayFormula(QUERY({StartingDates!D2:D, isnumber(match(StartingDates!D2:D,CancelledTours!D2:D,0))},"Select Col1 where Col2=false"))

Reversing the logic, this also works:

=ArrayFormula(QUERY({StartingDates!D2:D, isna(match(StartingDates!D2:D,CancelledTours!D2:D,0))},"Select Col1 where Col2=true"))

edited Dec 07 '19 at 12:26

answered Dec 07 '19 at 10:55

Tom Sharpe

1

Interesting, though irrelevant, that this bypasses the [date literal](https://developers.google.com/chart/interactive/docs/querylanguage#literals) "rule" in QUERY – Tedinoz Dec 07 '19 at 11:08
Good point, I didn't really think about that, but I wasn't sure until I tried it (confirmed by your reference) that literals true and false are allowed. I wondered if you could put 'where not Col2' - no you can't, 'not' only works with a boolean expression so you would have to say 'where not Col2=true':-) – Tom Sharpe Dec 07 '19 at 11:35

score 1 · Answer 2 · answered Dec 07 '19 at 11:28

1

try:

=FILTER(startingdates!D2:D, NOT(COUNTIF(Cancelledtours!D2:D, startingdates!D2:D)))

answered Dec 07 '19 at 11:28

player0

2 Answers2