Join Collect - Return Multiple Values from a Multi-Select Column

NiAlex
NiAlex ✭✭✭✭
edited 04/05/24 in Formulas and Functions

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

Answers

  • Kelly Moore
    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)), ",")

    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

  • NiAlex
    NiAlex ✭✭✭✭

    Hi @Kelly Moore

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

    Best,

    Nick

  • NiAlex
    NiAlex ✭✭✭✭

    Bumping in case anyone can answer

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • NiAlex
    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
    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
    KPH ✭✭✭✭✭✭

    Hi @NiAlex

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • NiAlex
    NiAlex ✭✭✭✭

    Thank you both!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!