admin管理员组文章数量:1322697
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
Share Improve this question edited Jan 15 at 2:15 Harun24hr 37.2k6 gold badges23 silver badges45 bronze badges asked Jan 15 at 1:06 Button OButton O 32 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
2 Answers
Reset to default 1QUERY()
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)
本文标签:
版权声明:本文标题:google sheets - Combining array_constrain and arrayformula and getting results in multiple columns - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742109895a2421197.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论