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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
That worked Andree, thanks for your help!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!