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

1
votes
=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?

Sheet- https://docs.google.com/spreadsheets/d/1zEQmNs48CsaaioQOcwuK97kcmnDLDfX-57nuXFaLujc/edit?usp=sharing

Screenshot

2
google-sheetsfiltergoogle-sheets-formulaarray-formulasgoogle-sheets-query
The reason that you are having problems with Column D is... it is the only one with any Cancelled Dates. - Tedinoz
your formula is just wrong and it works for A column just by coincidence - player0
I added the first tour date in col A and it worked, maybe I was mistaken though. Not at the computer now. - user198561

2 Answers

1
votes

try:

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

0

1
votes

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

enter image description here

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