How do I combine all cells based on specific text found in another cell?

Matt Foss

Hi Smartsheet community!

I have a from that our team uses to rank our vendors. The team selects 1st, 2nd, and 3rd place vendors and they have the option to leave comments for each vendor they select. As the results tend to involve the same three vendors, I need to find a way to "automatically" combine the comments into one cell.

As an example, let's say my first place result is Vendor X. I receive 6 votes for vendor X and there are comments associated with each. What kind of formula can I use that does the following: Based on the vendor column, for every matching vendor, combine the comments into one cell. Ideally, there would also be a way to clearly differentiate when a new comment starts (i.e. using a symbol like "]" after they are combined. I'd like it to be formula based so as more responses/results come in, they continually are updated/auto combined.

Screenshot for what I'm looking for as a result. Note that ideally the "Combined Comments" column is where the formula is based.

Andrée Starå

Hi @Matt Foss

Try something like this.

=IFERROR(JOIN(COLLECT([Submitted Comments]:[Submitted Comments], [First Choice]:[First Choice], [First Choice]@row), CHAR(10)), "")

The CHAR10 separates each comment on a new line in the cell.

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Matt Foss

Hey all, the good news is I found a solution in the form of the JOIN & COLLECT functions!

=IFERROR(JOIN(COLLECT([Submitted Comments]:[Submitted Comments], [First Choice]:[First Choice], [First Choice]@row)), "")

The only outstanding item I have for this, any suggestions how to incorporate a symbol of some kind between each comment?

Andrée Starå

Hi @Matt Foss

Try something like this.

=IFERROR(JOIN(COLLECT([Submitted Comments]:[Submitted Comments], [First Choice]:[First Choice], [First Choice]@row), CHAR(10)), "")

The CHAR10 separates each comment on a new line in the cell.

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Matt Foss

That worked Andree, thanks for your help!

Andrée Starå

@Matt Foss

Excellent!

You're more than welcome!