0
=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.

1
  • There is a superfluous comma after =FALSE, that causes the formula to error out. Jan 20, 2023 at 19:08

3 Answers 3

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 },)
0
=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

0

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 
)
1
  • I prefer Query as well but I have one column that contains a hyperlink to a ticket number that I'd like to preserve Jan 20, 2023 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.