-2

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

Improve this question
2
  • Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. Commented Jan 15 at 4:23
  • Hi Welcome, Could you clarify if the expected result would be by Column or by Row? Please provide the expected result so that we can answer your problem. Also if you could accept work around like Google Apps Script for possible answers. Commented Jan 15 at 10:08

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Awesome, that works too and giving me the same results as my array solution. Query is new to me. Thanks!
0

Add a filter for values in the prior columns.

So this would be the final column (K), which filters out the results already added in columns B, E and H:

=ARRAY_CONSTRAIN(arrayformula(filter('Guest List'!A7 :'Guest List'!A140, 'Guest List'!B7:B140 <> "refund", 'Guest List'!B7:B140 <> "cancelled", isna(MATCH('Guest List'!A7 :'Guest List'!A140, B10:B140,0)), isna(MATCH('Guest List'!A7 :'Guest List'!A140, E10:E140,0)), isna(MATCH('Guest List'!A7 :'Guest List'!A140, H10:H140,0)))),34, 1)
Improve this answer

2 Comments

I actually tried your formula on my end and it doesn't work on me. Can you enlighten the community on how it works to get values that can cross into 4 columns so that the community can relate or solve problems with the same issue. Thank you
What didn't work? For the first column i did =ARRAY_CONSTRAIN(arrayformula(filter('Guest List'!A7 :'Guest List'!A140, 'Guest List'!B7:B140 <> "refund", 'Guest List'!B7:B140 <> "cancelled")),34, 1) on the second column I added another condition to the filter, removing anything that appeared in the first column =ARRAY_CONSTRAIN(arrayformula(filter('Guest List'!A7 :'Guest List'!A140, 'Guest List'!B7:B140 <> "refund", 'Guest List'!B7:B140 <> "cancelled", isna(MATCH('Guest List'!A7 :'Guest List'!A140, B10:B140,0)))),34, 1) And kept adding further conditions to the filter as I went

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.