Google Sheets Query by Date and Group missing data

Question

I am trying to query a data set by date and return ID's that are not listed in the queried date range as compared to a master list. I have set up this Google Sheet as an example.

I know and understand how to use the query function to select data by the dates:

=query(Datasheet!$A$2:$B,"Select B where A > date '"&TEXT('Querysheet'!$A$3,"yyyy-mm-dd")&"' and A <= date '"&TEXT('Querysheet'!$B$3,"yyyy-mm-dd")&"' ...")

The part that I am missing is how to compare and return a group of ID's that are missing from the Datasheet compared with the Mastersheet.

I am looking for the exact opposite of the results from this formula for the date range 1/27/2021-2/1/2021. '=unique(query(Datasheet!$A$2:$B,"Select B where A > date '"&TEXT(Querysheet!$A$3,"yyyy-mm-dd")&"' and A <= date '"&TEXT(Querysheet!$B$3,"yyyy-mm-dd")&"'"))' The results should be 4444, 6666, 7777, 8888, 9999 — Noah

player0 player0 · Accepted Answer · 2021-02-10T00:16:16

use:

=UNIQUE(FILTER(Mastersheet!A2:A, NOT(COUNTIF(UNIQUE(QUERY(Datasheet!A2:B, 
 "select B 
  where A >  date '"&TEXT(Querysheet!A3, "yyyy-mm-dd")&"' 
    and A <= date '"&TEXT(Querysheet!B3, "yyyy-mm-dd")&"'")), Mastersheet!A2:A))))

Google Sheets Query by Date and Group missing data

1 Answers