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

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.


Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    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!


    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!


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

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!