google-sheetsarray-formulas

Combining array_constrain and arrayformula and getting results in multiple columns


I have a template where there's a guestlist on one tab.

On the print tab, I want to populate it with the guestlist, but not include those who show "refund" or "cancelled".

I've gotten that far already, but want it across 4 columns (B, E, H & K) for printing purposes, so I've constrained the array in the first column, but want to continue from that point on the next column and that's where I'm stuck.

https://docs.google.com/spreadsheets/d/17Y3T-JHJ5fCr1EyudZgjwxTGxA0jNMvFM3rx8Z6nMjQ

I've gotten the array_constrain and array formula working, I just need the multiple columns

=ARRAY_CONSTRAIN(arrayformula(filter('Guest List'!A7 :'Guest List'!A140, 'Guest List'!B7:B140 <> "refund", 'Guest List'!B7:B140 <> "cancelled")),34, 1)

I think it's similar to this question, but couldn't quite translate the answer to my scenario

arrayformula results go into next column after hitting array constraint and so on, in google sheets


Solution

  • QUERY() would be good choice for this case. Try-

    =QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' limit 34")
    

    For second result dataset use-

    =QUERY(QUERY('Guest List'!A7:B,"select A where not B matches 'Refund|Cancelled' offset 34"),"limit 34")
    

    So, above formula will drop first 34 records by offset 34 and will display next 24 records by limit 34. And you can continue for more columns just increasing offset multiply by 34 (suppose for 3rd column it will be offset 68).

    enter image description here