Combine Contacts from multiple columns into a single cell

Joe Goetschel

Hi @Karim Khan

Do you use Control Center/Data Mesh? Or are you on Core Smartsheet?

Kelly Ospina

@Olen Ronning I would love to have this feature to aggregate contact columns in a Control Center intake sheet for use in WorkApp.

chrisstrubemohawk

I would like the ability to concatenate multiple contact type fields into a single contact type field. The new process would need to have the ability to perform on both single select and multi-select contact column types.

Example:

For meeting attendance, I want to have 3 fields: Required, Optional, Informed

I want all 3 of the contents of those fields merged into one field [Assigned To].

spauliszyn

@chrisstrubemohawk, that is a great idea. If it is handled by a cell formula, we could build our own logic to allow this merge to be dynamic, depending on other conditions in our sheet data as well.

chrisstrubemohawk

Yes, I'm thinking of a few types of formulas based on the functions I have seen in Smartsheet so far.

=CONTACTMRGS([List 1]@row, [List 2]@row, etc.) for a situation where lists are a single selection.

=CONTACTMRGM([List 1]@row, [List 2]@row, etc.) for those rows that are multi select.

Of course, there have to be rules around it. The receiving Contacts column must already be properly configured to receive the possible selections. Or maybe the receiving column can't have the "Restrict to List Values Only" turned on. The how-to gets hairy very quickly. I hope it can be done.

Rich N

You have NO idea how helpful this would be.

The inability to combine contact lists is such a massive headache.

Brian_Richardson

Yes!

Although multiple selection contacts are captured as an array, they are presented as text and only work like text. This is a problem for multiple selection dropdowns too. Smartsheet has the data in array format in the backend (accessible via API) so let's have a couple of basic functions that let us get at it as an array.

Combining two or more email addresses with a delimeter like ; is a no-brainer for managing automation.

For a specific use case:

We have a task list for each of our customer implementation team's projects In that task list, there are certain key milestones/tollgates that need approval. The group of approvers is based on a number of conditions, primarily revenue level. Higher levels of revenue mean more people are combined into an approval group. Additionally, region/country plays a big role. We use groups of approvers at each approval stage to ensure that approvals happen as quickly as possible and don't bottleneck at any one person.

So for example, a Level 1 revenue project may need to have 2 people from the Region and 1 person from the Global team combined into the initial approval contact column to be sent an approval request.

We could setup a reference sheet with pre-set combinations of approvers, but the number of possible combinations of variables is in the hundreds if not thousands.

Javier Cuesta_UPL

As an alternative implementation, "groups" could be used for this. If in a multiple contacts column you could select groups, they would be a great way to handle row-level relevance filters, even beyond the 20 contacts currently possible in a cell. I can imagine this being rather a new column type.

Danielle Wilson

Cross posting @Brian_Richardson's post on how to get multi contacts to work in a few ways!

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

Lyz Liddell

I have a system where I have hundreds of components, each with the same list of tasks. Within each component, the same person is assigned to several different tasks, and some tasks have multiple people assigned. I'd love to have a column for each role so that I could assign them at the parent level, let the child rows auto-populate with =PARENT(), and then have the Assigned To column combine those into a single row via a formula. This way I'm typing only once per component, rather than manually adding users to 5, 10, 20+ rows per component.

Joe Goetschel

https://community.smartsheet.com/discussion/comment/422435#Comment_422435

@Danielle Wilson + @Brian_Richardson Brain can you add this to your post? This works with Core 😎

Brian_Richardson

@Joe Goetschel nice find! I did some testing and confirmed that if you Cell Link from a multiple contact column cell to a text column cell that contains a set of email addresses separated by commas, that the "destination" contact cell will correctly read it in as multiple individual contacts. Great catch and I'll add it to the article.

However, in testing, using a cross-sheet reference did not work. I did =INDEX({Text Column},1) to pick up the first cell value containing two email addresses separated by commas, and set the destination column to be multiple contacts. The data still came in as plain text. I also tried with CHAR(10) as the separator, which works for multiple selection dropdowns, but that didn't work either.

What conditions did you use to get a VLOOKUP or INDEX to work and pull in text as contacts?

ktcran

We have several different contact columns for the various employees assigned to a row/request per department. It would be nice to combine these for more concise reporting.