1

So I'm trying to filter by a number of things (age, matching codes, a non-null DOB (not needed in this particular sheet as it's a subtraction and < operation on age, but I left it in for a following sheet that's basically the same formula & filtering conditions but doing a > operation, so as to not find ages from null entries)). I've gotten the query to work on all of the conditions I'm filtering by except for Col16.

=query(filter(all!A:AR, Match(all!O:O, OffenseCodes!A481:A486), DATEDIF(all!R:R,all!Q:Q, "Y") < 18 ), "select * where Col18 is not null AND (Col16 = 'NC' or Col16 = 'G')",1)

I've tried filtering Column 16 separately, I've tried embedding the condition elsewhere in the query.

the results I get are either every row including ones that are trying to be filtered out, no matches, or an error about filter range mismatch if I try to embed the condition in the range of the filter.

those separate attempts being:

=FILTER(all!A:AR, all!P:P <> "NG", all!P:P <> ERROR.TYPE(1/0)) =FILTER(all!A:AR, all!P:P <> "NG", all!P:P <> " ")

and

=query(filter(all!A:AR, Match(all!O:O, OffenseCodes!A481:A486), DATEDIF(all!R:R,all!Q:Q, "Y") < 18 , MATCH(all!P:P, A2:A3,0) ), "select * where Col18 is not null AND (Col16 = 'NC' or Col16 = 'G')",1)

returns the row with a null even if I specify and Col16 <> ''

=filter(all!A:AR, Match(all!O:O, OffenseCodes!A481:A486), DATEDIF(all!R:R,all!Q:Q, "Y") < 18 , MATCH(all!P:P,A2:A3,0))

^ returns just rows with NC in Col16, not G

Also, one of the separate filter attempts that just loaded somewhat works but it returned no results with G in column 16, even though row 6402 fits the age requirement (2012-1997 = 15, which is <18), plea type (G), and offense code (3800, ... 3805, in this case 3803). row 6726 also fits.

=filter(all!A:AR, COUNTIF(all!A:AR, (MATCH(all!P:P,A1:A2,0))))

This a copy of the sheet, with the unfiltered data being "all" and one of the filters being column 1 in "OffenseCodes"

https://docs.google.com/spreadsheets/d/1ynHc8hdDxsyfmkyan7kny-ONLz-VHNz5ksIKY3FpVlM/edit#gid=383014835

Here's an image of the return that's bad/ignoring the filter conditions.

query returning a null with desired values

Any assistance or guidance to solve this is appreciated; I've removed values from columns that are unneeded here, I.E people's names.

blob
  • 11
  • 2

1 Answers1

0

You have a trailing space for Plea Code G_ in all tab. Do test the formula added in Cell_A35 of combined filter tab

=filter(all!A:AR, xmatch(all!O:O,OffenseCodes!A481:A486), datedif(all!R:R,all!Q:Q, "Y")<18 , xmatch(trim(all!P:P),A2:A3))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • Thank you. I thought I'd replaced all the spaces in there by using ctrl + F replace, but apparently that didn't get all of them (different number of spaces or it auto-formatting with a space or I don't know). Trim clearly works a lot better than ctrl+F replace. – blob Aug 01 '23 at 14:12