2

So I have this formula, and it's working as intended but I would like to further refine the data.

Formula:

=QUERY('Users'!A1:Q, "Select A,B,C,F,G,O,Q where Q >= 180 and Q < 44223")

I have tried:

=QUERY('Users'!A1:Q, "Select A,B,C,F,G,O,Q where Q >= 180 and Q < 44223 and F not like '*text*'")

But that caused an error. Ideally, I would like to ommet any results that match partial texts in columns C and F. Any ideas?

EDIT: Included a link to an example sheet

Ideally, I want the query to match everything in column F except 'Archived' (but needs to be wildcarded) and everything in column C except Delta (again, needs to be wildcarded)

player0
  • 124,011
  • 12
  • 67
  • 124
SL8t7
  • 617
  • 2
  • 9
  • 27

1 Answers1

3

try:

=QUERY(Sheet1!A1:Q6, 
 "select A,B,C,F,G,O,Q 
  where Q >= 180 
    and Q < 44223 
    and not lower(O) matches '.*archived.*' 
    and not lower(C) matches '.*delta.*'")
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi, unfortunately, that doesn't filter out the results that match the keyword. If my keyword is 'magic', for example and the column contains 'black magic' and 'white magic' and axe - I would only want to return those that match axe. This is an example, so just matching axe wouldn't do – SL8t7 Jan 27 '21 at 13:34
  • @SL8t7 can you share a copy of your sheet? – player0 Jan 27 '21 at 13:36
  • I have added a link to the example sheet – SL8t7 Jan 27 '21 at 13:50
  • Also have tried to better explain what I am trying to get done – SL8t7 Jan 27 '21 at 13:54