Concatenate two columns from JOIN/COLLECT Condition
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
Best Answer
-
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.
Answers
-
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.
-
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
-
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.
-
Thanks, Paul. Appreciate your help and I will follow your guideline.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!