How to get Join Collect formula to work

Dakjoekipe · 07/03/24

I have a sheet (picture attached) where I am trying to show all of the tasks dependent upon a certain task from being done. I want to sue a join Collect formula to list out all of the tasks referenced in the the predecessor column that match the row #. For example, in the picture, Row 70 would show the task "eComm Mezz Calcs to Building Eng" under the "list of Dependent Tasks v2" column since 70 is listed in the predecessor column. I added the helper rows to ensure both the predecessor and row columns act as text, and I was able to get a Match function to work under "list of dependent tasks" as proof of concept that it shouldn't be a cell type issue.

Appreciate any support in advance.

Kelly Moore · 07/05/24

Hey @Dakjoekipe

Try this:

=JOIN(COLLECT(Task:Task, [Predecessor Helper]:[Predecessor Helper], CONTAINS([Row # Helper]@row, @cell)), " , ")

The " ," at the end is the delimiter - the separator between textstrings so it doesn't run all together. If you don't want this, you can delete all the entries and have all three closing parentheses stacked all together. If instead of a comma and spaces between entries, let me know if you would rather have it as line breaks.

Will the formula work for you?
Kelly

Kelly Moore · 07/05/24

Hey @Dakjoekipe

Try this:

=JOIN(COLLECT(Task:Task, [Predecessor Helper]:[Predecessor Helper], CONTAINS([Row # Helper]@row, @cell)), " , ")

The " ," at the end is the delimiter - the separator between textstrings so it doesn't run all together. If you don't want this, you can delete all the entries and have all three closing parentheses stacked all together. If instead of a comma and spaces between entries, let me know if you would rather have it as line breaks.

Will the formula work for you?
Kelly

Dakjoekipe · 07/08/24

@Kelly Moore Thank you Kelly, that worked perfectly. Appreciate the help!

How to get Join Collect formula to work

Best Answer

Answers

Help Article Resources

Categories