JOIN(DISTINCT(COLLECT())) without losing Contact List column functionality

Kyle ChipmanKyle Chipman ✭✭✭✭✭
edited 01/19/21 in Formulas and Functions
01/19/21 Edited 01/19/21
Accepted

Howdy - is there a formulaic way to gather all contact list column values alongside DISTINCT(COLLECT())? JOIN eliminates the contact list returns, converting them to strings, and trying something more convoluted via the Name + "<" + Email + ">" advice at the bottom of this post

https://community.smartsheet.com/discussion/67503/formula-to-combine-name-and-email-to-create-a-contact-row

requires a change of column format to initiate. Would like to run Current User reporting and build automations off a multi-value contact cell. I can provide context if helpful, just wasn't sure if I was overlooking something straightforward that's a simple answer first.

Thanks kindly!

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Unfortunately there is no functionality to populate multiple entries into a contact list column while still maintaining the contact type functionality.


    We can populate the data, but it will be as a text string.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Kyle Chapman ,

    I don't think there's an easy way to do what you want. Contact list entries are challenging to manipulate. I suspect you'll end up using helper columns and possibly a helper sheet to compile what you're looking for. There are lots of smart people in the Community. Maybe someone has an idea for you.

    Mark

  • Kyle ChipmanKyle Chipman ✭✭✭✭✭

    Bummer. I tried something along the lines of a helper sheet, running our MASTER - Employee table with auto-row ID's to split name and email, then converting in and around the formula pasted at the top. Again, it required that I change column format types away-from-then-back to Contact List to register the values as contacts, which will not work for my intended goal.

    Appreciate the response!

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Kyle Chapman ,

    Place this formula in a Contact List column and see if it gives you what you're looking for:

    =JOIN(COLLECT({insert contact list range}, CHAR(10))

    It won't remove duplicates but might get you on the right path.

    Mark

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Unfortunately there is no functionality to populate multiple entries into a contact list column while still maintaining the contact type functionality.


    We can populate the data, but it will be as a text string.

  • Kyle ChipmanKyle Chipman ✭✭✭✭✭

    Thanks Mark and Paul - appreciate the responses.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Kyle Chipman Happy to help. 👍️


    @Mark Cronk CHAR(10) is the delimiter for a multi-select DROPDOWN column. The delimiter in a multi-select CONTACT type column is actually ", " (comma space). If we are ok with converting to a text string, using the DISTINCT function should weed out duplicates. Another option for that (since it will be output in a text string anyway) would be to just JOIN/COLLECT and then throw in a SBSTITUTE to replace the ", " with CHAR(10) and drop it in a multi-select dropdown type column. Dropping a string into a multi-select dropdown column with the CHAR(10) delimiter treats each piece as an individual selection within the cell and the multi-select dropdown will automatically weed out the duplicates.

Sign In or Register to comment.