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

Kyle Chipman

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!

Paul Newcome

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.

Mark 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 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 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 Newcome

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 Chipman

Thanks Mark and Paul - appreciate the responses.

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