How to combine multiple values in a cell based on unique identifier

Katey N.

Hello,

I've been reading through many different Smartsheet forum threads on how to do various formulas using Index / Collect / Join / Match functions, however, I have been unable to adapt any of the formulas I'm finding to do what I need to do. Everything either shows up blank or gives me error messages.

Goal

If there are multiple entries on my source sheet for a specific Member ID, then I want to combine all of the different e-mail addresses for that member on my source sheet into a single cell on my target sheet if the Member IDs match. I've already lost count of how many different formulas I've tried, but this is the most recent formula I tried that I found in a forum for someone doing something similar.

=JOIN(COLLECT({Email address}, {Member ID}, [Member ID]@row), " , ")

Source Sheet

Target Sheet

I want the email addresses to go into the e-mail column for the correct Member ID in the following format "email 1, email 2, email 3." I don't need duplicates, though, so I if someone on the source sheet has multiple entries but they used the same email address each time, then the target sheet should just display "email 1."

After doing joining the email address, I want to do the same thing in the State column if anybody on the source sheet has multiple states added.

Thank you so much for any help you can provide!

-Katey

DKazatsky2

Hi @Katey N.,

Try this: =JOIN(DISTINCT(COLLECT({Email address}, {Member ID}, [Member ID]@row)), " , ")

You can then replicate this for State as well.

Hope this helps,

Dave

DKazatsky2

@Katey N.,

I just had a thought. The Member ID in my example is a number, in your example it looks like it's a string. Make sure they are identical in both sheets - look for any leading spaces.

Hoping this leads somewhere.

DKazatsky2

Hi @Katey N.,

Try this: =JOIN(DISTINCT(COLLECT({Email address}, {Member ID}, [Member ID]@row)), " , ")

You can then replicate this for State as well.

Hope this helps,

Dave

Katey N.

Hi @DKazatsky2 , thank you for your response! I tried your formula and now I'm getting the following.

DKazatsky2

Hi @Katey N. ,

Make sure all your cross-sheet references are correct. The formula is working properly for me.

Katey N.

@DKazatsky2 I am baffled then! I removed my cross-sheet references and did them fresh. It's still not populating the information. It's not even showing the comma now, it's just totally blank.

DKazatsky2

@Katey N.,

I just had a thought. The Member ID in my example is a number, in your example it looks like it's a string. Make sure they are identical in both sheets - look for any leading spaces.

Hoping this leads somewhere.

Katey N.

@DKazatsky2 Dave, you are an incredible human. That worked! I had no clue that the original data file I was provided had hidden apostrophes in the number string. I was able to create a helper column to convert the 1k+ Member IDs to a regular text string, reworked my formula on the target sheet to check the Member ID against the converted Member ID on the source sheet instead, and voilà! Thank you SO SO SO much!

DKazatsky2

@Katey N.,

Awesome - glad you got it working.