google sheets - Combining array_constrain and arrayformula and getting results in multiple columns - Stack Overflow

admin管理员组

文章数量:1323029

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.

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

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/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

• google-sheets
• array-formulas

Share Improve this question Follow edited Jan 15 at 2:15 Harun24hr 37.2k66 gold badges2323 silver badges4545 bronze badges asked Jan 15 at 1:06 Button OButton O 322 bronze badges 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. – TheMaster 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. – Patsytalk Commented Jan 15 at 10:08

Add a comment  | 

2 Answers 2

Sorted by: 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).

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)

本文标签：