JOIN COLLECT with a not condition

Kate H

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.

Kerry St. Thomas

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.

Kate H

@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.