JOIN(COLLECT formula
Best Answers
-
I'm trying to do something very similar and not having any luck. I need the Job Titles(s)(Formula) column in sheet 1 to populate the Job Title from sheet 2 based on the traveler/employee name. This isn't difficult when there is a 1 to 1 relationship. But we have trips with multiple travelers and I cannot get this to work. I've tried:
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, CONTAINS(@cell, [Traveler(s) Name, If known (select)]@row)), ",") brings back a blank cell for all rows, even those with only one traveler.
=IFERROR(INDEX(COLLECT({Employee List Job Title}, {Employee List Employee Name}, [Traveler(s) Name, If known (select)]@row), 1), "No Match Found") brings back correct information for trips with one employee. Multiple employees come back as "No Match Found".
Any assistance would be greatly appreciated.
Sheet 1
Sheet 2
-
Yes. CONTAINS doesn't play well with Contact type columns. The HAS function will be needed, but you will need to flip the two pieces of it around I believe.
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, HAS([Traveler(s) Name, If known (select)]@row, @cell)), ", ")
Answers
-
I'm trying to do something very similar and not having any luck. I need the Job Titles(s)(Formula) column in sheet 1 to populate the Job Title from sheet 2 based on the traveler/employee name. This isn't difficult when there is a 1 to 1 relationship. But we have trips with multiple travelers and I cannot get this to work. I've tried:
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, CONTAINS(@cell, [Traveler(s) Name, If known (select)]@row)), ",") brings back a blank cell for all rows, even those with only one traveler.
=IFERROR(INDEX(COLLECT({Employee List Job Title}, {Employee List Employee Name}, [Traveler(s) Name, If known (select)]@row), 1), "No Match Found") brings back correct information for trips with one employee. Multiple employees come back as "No Match Found".
Any assistance would be greatly appreciated.
Sheet 1
Sheet 2
-
If I remember correctly you will need to use the HAS formula in place of the Contains formula. The Contains doesn't work well with Contact Columns
-
The below should work if you make your formula column a multiple select column
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, HAS(@cell, [Traveler(s) Name, If known (select)]@row)), ", ")
-
Yes. CONTAINS doesn't play well with Contact type columns. The HAS function will be needed, but you will need to flip the two pieces of it around I believe.
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, HAS([Traveler(s) Name, If known (select)]@row, @cell)), ", ")
-
@Hollie Green Thank you so very much!. Unfortunately, while this works for single contact lines, I still get no result for multiple contacts. Here are the details, if you have time to take a look and let me know where I've gone wrong.
Sheet 1 has a contact column that allows multiple contacts and restricts to list values only. Column name is Traveler(s) Name, if known(select)
Sheet 1 has a column Job Title(s) (Formula) which is a text column.
Sheet 2 is a support sheet, Employee List. Two columns are being used in the formula, Job Title is a text column. Employee Email is a contact column restricted to one contact per cell. (I did change this to multi, it didn't solve the problem).
This is the formula used in the Job Title(s) (Formula) column below,
=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Email}, HAS(@cell, [Traveler(s) Name, If known (select)]@row)), ",")
SHEET 1
Sheet 2
-
-
Happy to help. 👍️
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!