Filter formula: Exclude data without a timestamp

Question

=sort( 
  filter( 
    { Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },
    regexmatch({ Bryson!Q4:Q502; David!Q4:Q502; Justin!Q4:Q502; Michael!Q4:Q502; Manny!Q4:Q502; ScottC!Q4:Q502; ScottD!Q4:Q502; Zhenqian!Q4:Q502; Visitors!Q4:Q502 }, "Y"),
    regexmatch({ Bryson!R4:R502; David!R4:R502; Justin!R4:R502; Michael!R4:R502; Manny!R4:R502; ScottC!R4:R502; ScottD!R4:R502; Zhenqian!R4:R502; Visitors!R4:R502 }, "")=FALSE,
  ), 
  18, 1,6,1 
)

Ideally, I want to filter out entries in A4:S502 on all these sheets that don't have a timestamp (Column R), but as long as R is not blank, that would be good enough, as I have an apps script do the timestamping and it should either be nothing, or a date. The first filter criteria for column Q works, but would need this second criteria for R.

My current formula throws the error "FILTER has mismatched range sizes. Expected row count: 4491. column count:1, Actual row count: 1, column count: 1.

Answer 1

If you're just comparing with values, you can just use the comparison symbols without REGEXMATCH:

=sort( 
  filter( 
    { Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },
    { Bryson!Q4:Q502; David!Q4:Q502; Justin!Q4:Q502; Michael!Q4:Q502; Manny!Q4:Q502; ScottC!Q4:Q502; ScottD!Q4:Q502; Zhenqian!Q4:Q502; Visitors!Q4:Q502 }="Y",
    { Bryson!R4:R502; David!R4:R502; Justin!R4:R502; Michael!R4:R502; Manny!R4:R502; ScottC!R4:R502; ScottD!R4:R502; Zhenqian!R4:R502; Visitors!R4:R502 }<>""
  ), 
  18, 1,6,1 
)

Another option if you have so many sheets is use. LAMBDA and INDEX to refer to columns by number:

=LAMBDA(range,
  sort( 
      filter( 
        range,
        INDEX(range,,17)="Y",
        INDEX(range,,18)<>""
      ), 
      18, 1,6,1 
    ))
({ Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },)

Answer 2

=sort( 
  filter( 
    { Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },
    regexmatch({ Bryson!Q4:Q502; David!Q4:Q502; Justin!Q4:Q502; Michael!Q4:Q502; Manny!Q4:Q502; ScottC!Q4:Q502; ScottD!Q4:Q502; Zhenqian!Q4:Q502; Visitors!Q4:Q502 }, "Y"),
    { Bryson!R4:R502; David!R4:R502; Justin!R4:R502; Michael!R4:R502; Manny!R4:R502; ScottC!R4:R502; ScottD!R4:R502; Zhenqian!R4:R502; Visitors!R4:R502 }>DATE(2000,1,1)
  ), 
  18, 1,6,1 
)

Figured it out. I had an extra comma after the second criteria so my previous attempt to fix it wasn't successful. The syntax I have here filters by everything after Jan 1 2000 so it'll effectively just give me anything with a timestamp

Answer 3

Use query(), like this:

=query( 
  { Bryson!A4:S; David!A4:S; Justin!A4:S; Michael!A4:S; Manny!A4:S; ScottC!A4:S; ScottD!A4:S; Zhenqian!A4:S; Visitors!A4:S }, 
  "where Col17 contains 'Y' and Col18 is not null order by Col18, Col6", 0 
)