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:

Find more posts tagged with

Formulas

Join

collect

Accepted answers

Paul Newcome

CONTAINS does not work on a contact type column. You have to use the HAS function.

All comments

Kelly Moore

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

=JOIN(COLLECT({Clubhouse Team Members Department}, {Clubhouse Team Members Names}, FIND([Assigned To]@row,@cell)>0), ",")

Will either of these work for you?

Kelly

NiAlex

Hi @Kelly Moore

Unfortunately not, both of those formula variations also returned a blank result

Best,

Nick

NiAlex

Bumping in case anyone can answer

KPH

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.🤞

NiAlex

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

NiAlex

@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!

Paul Newcome

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

NiAlex

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!

NiAlex

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

KPH

Hi @NiAlex

Sorry, I did not pay enough attention to your first screen shot! Glad you managed to get it sorted.

Paul Newcome

CONTAINS does not work on a contact type column. You have to use the HAS function.

NiAlex

Thank you both!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.