I'm using QUERY to fetch a list of emails.
Additionally, I need to exclude a couple of non relevant emails. The list of non relevant emails is pretty long (4k +) so instead of manually typing each email, I included an array inside my QUERY.
Here is the formula I came up with:
=QUERY('Raw Data'!A:C,"SELECT * WHERE NOT Col1 MATCHES '"&ARRAYFORMULA(TEXTJOIN("|",TRUE,".*"&'List of emails Exclusion'!A2:A&".*"))&"'")
However TEXTJOIN does not allow more than 50k characters, so I get an error.
I'm guessing I need to nest this inside another QUERY but for some reasons I cannot get it right. Would appreciate any help on this.
Here is my dummy sheet to help understanding my issue.
Here's one approach you may test out:
=filter('Raw Data'!A2:C,isna(xmatch(regexextract('Raw Data'!A2:A,"^(?:.*@)?(.*)"),tocol('List of emails I want to exclude'!A2:A,1))))