Exclude Multiple Different Cells When Importing Data
I am trying to filter(query()) some specific columns into a spreadsheet where I do some automatic calculations. However, there are 8 or so specific strings that I don't need and clutter the calculations page. I have a list of these strings that I don't need but I can't seem to EXCLUDE these strings from the filter(query()) without creating a 15 line block of text.
What I've Tried:
=FILTER(QUERY(A:C,"select A,C"),QUERY(A:C,"select A")<>D2,D3,D4,D5)
=FILTER(QUERY(A:C,"select A, C"),QUERY(A:C,"select A")<>{D2,D3,D4,D5}) - I have also tried this one joining the text w/ a "," so that there is one value
=FILTER(QUERY(A:C,"select A, C"),QUERY(A:C,"select A")<>D2) - What is silly is THIS ONE VALUE works, but I cant add multiple values without adding 7 more conditions to the filter which would exclude the values that I need excluded, but I would have to change the formula manually and it would be monstrous when I keep adding strings to remove.
The goal is to be able to be able to add another string to a separate cell and have that be excluded as well.
Here is the google sheet: https://docs.google.com/spreadsheets/d/1u-Po1Oae4MTYU10o_-1Yu0s9Ras9JUn4-SFzTkmMldI/edit?usp=sharing
Suggestion
Perhaps you can try using REGEXMATCH so you could add multiple words to be matched but in the reverse way (via placing the REGEXMATCH in a NOT function) to FILTER your data more efficiently as seen below:
=FILTER(QUERY(Sheet1!A:C,"select A, C"),NOT(REGEXMATCH(Sheet1!A:A,"Value 7|Value 3|Value 10|Value 8")))
Alternatively, you could also reference the range of cells you want to exclude in the REGEXMATCH via JOIN function like:
=FILTER(QUERY(Sheet1!A:C,"select A, C"),NOT(REGEXMATCH(Sheet1!A:A,JOIN("|",D2:D5))))
[UPDATE]
If you're using an IMPORTRANGE in your actual sheet formula, then you would need to structure it like this sample. The REGEXMATCH formula also contains a range:
=FILTER(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXX","Sheet1!A:C"),"select Col1, Col3"),NOT(REGEXMATCH(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXX","Sheet1!A:C"),"select Col1"),"Value 7|Value 3|Value 10|Value 8")))
Demo
- Sample Test
- The referenced Sheet 1
