JOIN(COLLECT formula

System · 05/25/23

This discussion was created from comments split from: Join Collect formula for multiple values.

Stephanie Allison · 05/25/23

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

Paul Newcome · 05/25/23

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)), ", ")

Stephanie Allison · 05/25/23

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

Hollie Green · 05/25/23

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

Hollie Green · 05/25/23

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)), ", ")

Paul Newcome · 05/25/23

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)), ", ")

Stephanie Allison · 05/25/23

@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