Join Collect - Return Multiple Values from a Multi-Select Column
Hi all,
I have a change log where the "Assigned To" column has multiple assignees - I have another column where I would like to return the department(s) of the assignees, which is pulled from another sheet.
However, when I use the following formula, it returns a blank result when I put two people in the "Assigned To" column:
=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, HAS(@cell, [Assigned To]@row)), ",")
When I only have one person in the "Assigned To" column, the correct result is returned.
The main sheet:
And the sheet with the team members and department names:
Best Answer
-
CONTAINS does not work on a contact type column. You have to use the HAS function.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hey @NiAlex
See if either of these will work for you
=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, CONTAINS([Assigned To]@row,@cell)), ",")
or
=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, FIND([Assigned To]@row,@cell)>0), ",")
Will either of these work for you?
Kelly
-
Hi @Kelly Moore
Unfortunately not, both of those formula variations also returned a blank result
Best,
Nick
-
Bumping in case anyone can answer
-
Hi @NiAlex
How about:
=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, CONTAINS(@cell, [Assigned To]@row)), ", ")
I started from scratch, tested it and it seems to do what you want. Comparing it to yours, I think the only difference is I am using CONTAINS not HAS, which does mean if any of the names are within someone else's name there could be an issue. However, I think this is the only way to do what you want*. It's also similar to @Kelly Moore 's first suggestion, but with slightly different syntax.
Have a go! It works for me:
Result
Source Data
* My concern may be unfounded, it seems to be working for names that are parts of names (I added im Can to the source data in the invention department, but did not add Homer).
Let me know how you get on.🤞
-
Thank you! From looking at your screenshots, I figured out the issue - you had the names on a dropdown list, while I was attempting to use the contact list (so people would get a notification that they were assigned).
However, I don't think there is a way to get the contact list type column to work with this formula. The workaround I came up with is two "Assigned To" columns - "Assigned To (name only)" & "Assigned To (contact)":
By having the formula refer to the "Assigned To (name only)" column, I was able to get the formula to work. Not the best possible solution - it would be cleaner to have one column only, but now the sheet / formula and reporting in the dashboard can work as needed with the extra column, so it will do until Smartsheet plays nicer with the Contact-type column.
Once again, thank you! @KPH
-
@KPH another question for you - now I am trying to count on another sheet, the number of change log items an individual has - I am using the following formula but returning a 0 result:
=COUNTIFS({Change Log Assigned To}, CONTAINS([Primary Column]@row, {Change Log Assigned To}), {Change Log Open / Close}, "OPEN")
I am trying to count from the previous sheet's "Assigned To (name only)" column - if the team member's name is contained within the "Assigned To (name only)" column cell, I would like the countifs formula to add +1 to their change log total
Thank you (again) in advance!
-
Try this for your JOIN/COLLECT referencing your original multi-select contact type column:
=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, HAS([Assigned To]@row, @cell)), ",")
Your COUNTIFS would look more like this:
=COUNTIFS({Change Log Assigned To}, CONTAINS([Primary Column]@row, @cell), {Change Log Open / Close}, "OPEN")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
hi @Paul Newcome - thank you! I was able to get the "Assigned To - Department Test" column to display the correct department names tied to personnel names using the multi-select contact type column:
Unfortunately, for the COUNTIFs formula, where adding up the # of change log items assigned to an individual, the formula still returns a "0" result. My guess is the formula is looking for a match in the multi-select contact column that matches that individual exactly, but all columns have multiple contacts selected, so there is no exact match.
I know the {Change Log Open / Close} portion of the formula works, because there are 5 items open and when I eliminate the rest of the formula, a value of "5" is returned.
=COUNTIFS({Change Log Assigned To}, CONTAINS([Primary Column]@row, @cell), {Change Log Open / Close}, "OPEN")
Thank you again in advance!
-
@Paul Newcome another note - when I change the {Change Log Assigned To} reference to the dropdown multi-select column (not the contact-type column), the formula works!
=COUNTIFS({Change Log Assigned To}, CONTAINS([Primary Column]@row, @cell), {Change Log Open / Close}, "OPEN")
-
Hi @NiAlex
Sorry, I did not pay enough attention to your first screen shot! Glad you managed to get it sorted.
-
CONTAINS does not work on a contact type column. You have to use the HAS function.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you both!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!