Concatenate two columns from JOIN/COLLECT Condition

Chitta

Hi

I would like to concatenate 2 columns based on my JOIN/Collect condition, I am able to achieve only partially but not fully expected result. Appreciate if someone can help in this query

I am collecting Task name based on following condition:

=JOIN(COLLECT([Task Name], {Status}, <>"Complete", {Release}, "Yes", [End Date], <=TODAY(30)), [End Date]@row + CHAR(10) + CHAR(10))

Result:

Task 2

Task 3

Instead of only task name I would like to End Date concatenated with Task Name:

3/20/2022 Task 2

4/30/2022 Task 3

Kindly Help!

~Chitta

Paul Newcome

There is another way, but it is much more complex and still requires adding columns to the source sheet. Since you are going to have to add at least one column to the source sheet regardless of the solution, you may as well go the easier route and add just one that pulls those two values together to begin with.

Paul Newcome

You would need to include a column in the source sheet that joins the two together on a row by row basis and then reference this helper column in your cross sheet JOIN/COLLECT.

Chitta

Thanks Paul for the solution, is there any other way of achieving my need cos I have around 20 project plans sheets for various projects and wondering if we can find a way through formulas then adding additional column.

Thanks in advance

~Chitta

Paul Newcome

There is another way, but it is much more complex and still requires adding columns to the source sheet. Since you are going to have to add at least one column to the source sheet regardless of the solution, you may as well go the easier route and add just one that pulls those two values together to begin with.

Chitta

Thanks, Paul. Appreciate your help and I will follow your guideline.

Paul Newcome

Happy to help. 👍️