JOIN COLLECT with a not condition
I'm trying to look within a column and identify and collect all answers that meet the condition into one parent cell
=JOIN(COLLECT(Group1, Group1, NOT("no")))
This formula is in the parent cell of a column titled Group1. I want to look in group 1 at all its children, find all the instances where the cell content isn't "no" and collect them in the parent cell. I tried doing ….NOT(Group1@row="no"))) but it didn't work either.
Answers
-
Try adding a single row context - similar to what you tried to do with the second solution
=JOIN(COLLECT(CHILDREN(),CHILDREN(), NOT(HAS(@cell,"no"))))You can play with "HAS" (that's what's in the entire cell) versus "CONTAINS" (that's what is in part of the cell) to determine which is the best for your use case.
I'm trying to figure out how you might use this - Assuming Group1 is a column of Yes and No in the children, and you have 5 rows - 2 with No, 3 Yes - you'll end up with YesYesYes as your fomula output. JOIN will basically string together the cells and create a big string. I wonder if there's a different thing you might be able to drill into - for example the count of not-no responses, or something similar.
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
@Kerry St. Thomas thank you so much!
Yes I'm trying to protect non public info so names and things are changed. Essentially I have a list of groups, then trying to have a column that shows the group name or no (like group is not included). Then this formula will take the names of the ones that aren't no which will be group names and concatenate so you have a list of all the ones involved by column to use for auto notifications message content.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!