Joining two index/match into single multi-contact column

Hello,

I currently have two columns that are pulling in from a separate resource that contains multi-contact columns. My hope is to cut down on the separate columns and combine them into one but when I try adding the two formulas under a single column, I no longer see the email contact formatting that I would in the other.

My formula is currently:

=index({Email}, Match([@[Building]], {Building}, 0),0) + "; "+ index({Email 2}, Match([@[Group]], {Group}, 0),0)

I've tried replacing the "; " with a "CHAR(10)" to see if it would work but there was no change.

Any one have any ideas? Worst comes to worst I can keep the two columns but it isn't the preference since I have an exclusions list I need to also incorporate and would need to add two more columns if I can't figure that out. Any help would be appreciated!

Find more posts tagged with

Formulas

Accepted answers

SoS | Dan Palenchar

Unfortunately contact columns are limited in that you cannot use a formula to combine multiple contacts together.

You can pull in the contacts from one contact cell into another whether it be a single contact in the cell or multiple.

So if it makes sense you could make a lookup table with contact groups (one row per group) and use a lookup formula to pull in the complete group.

@kioshi43

jmyzk_cloudsmart_jp

Hi @kioshi43

Combining multiple contact columns into a single column in Smartsheet with the core product is currently not supported. Many users have requested this feature. Users often have to use workarounds or external tools to achieve this functionality.

1. Product Idea

The ability to combine multiple contact columns into a single contact column is a highly requested feature in the Smartsheet community. Many users have expressed the need for this functionality.

2. Workarounds and Limitations

Text Columns

Current workarounds involve turning contact columns into text columns, combining them with formulas, and then converting them back to contact columns.

Control Center or Data Mesh

If you're using Control Center or Data Mesh, you can leverage these tools to automate the process and combine contact columns as part of your workflow. This is a more complex solution but can be effective for large-scale implementations.

JOIN(COLLECT) Formula and Data Shuttle

Another workaround involves using the JOIN(COLLECT) formula to concatenate email addresses as a text string and then using Data Shuttle to move the data back into a multi-contact column.

Steps:

Change the contact columns you want to combine into text columns.
Use the JOIN(COLLECT) formula to concatenate the email addresses.
Use Data Shuttle to move the concatenated data back into a multi-contact column.

3. Possible with API

Combining contact columns is more feasible using the Smartsheet API. You can write a script that:

Retrieves the data from the specified columns.
Combines the contacts programmatically.
Updates the Smartsheet with the combined contact information.

This approach allows for more flexibility and automation, ensuring that the contact formatting is preserved.

The following video demonstrates such API solutions. (Note: Since you can update multiple dropdown lists with the core functionality, once you create multiple email lists, you can use the solution to create multiple contact lists.)

https://www.youtube.com/watch?v=IkX2EqTFwlY

All comments

SoS | Dan Palenchar

Unfortunately contact columns are limited in that you cannot use a formula to combine multiple contacts together.

You can pull in the contacts from one contact cell into another whether it be a single contact in the cell or multiple.

So if it makes sense you could make a lookup table with contact groups (one row per group) and use a lookup formula to pull in the complete group.

@kioshi43

jmyzk_cloudsmart_jp

Hi @kioshi43

1. Product Idea

The ability to combine multiple contact columns into a single contact column is a highly requested feature in the Smartsheet community. Many users have expressed the need for this functionality.

2. Workarounds and Limitations

Text Columns

Current workarounds involve turning contact columns into text columns, combining them with formulas, and then converting them back to contact columns.

Control Center or Data Mesh

JOIN(COLLECT) Formula and Data Shuttle

Another workaround involves using the JOIN(COLLECT) formula to concatenate email addresses as a text string and then using Data Shuttle to move the data back into a multi-contact column.

Steps:

Change the contact columns you want to combine into text columns.
Use the JOIN(COLLECT) formula to concatenate the email addresses.
Use Data Shuttle to move the concatenated data back into a multi-contact column.

3. Possible with API

Combining contact columns is more feasible using the Smartsheet API. You can write a script that:

Retrieves the data from the specified columns.
Combines the contacts programmatically.
Updates the Smartsheet with the combined contact information.

This approach allows for more flexibility and automation, ensuring that the contact formatting is preserved.

https://www.youtube.com/watch?v=IkX2EqTFwlY

kioshi43

Thank you for the confirmation everyone - I'll try exploring some of those options. Appreciate it!

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.