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


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.


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!


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!