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

Matt Foss
Matt Foss ✭✭
inFormulas and Functions

Hi Smartsheet community!

我有一个从我们的团队使用k 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.

image.png


Tags:
· Share on Facebook Share on Twitter

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!

    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 bymarking 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:[email protected]| P: +46 (0) - 72 - 510 99 35

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

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @haliecarr<\/a> ,<\/p>

Give this a try.<\/p>

=SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report Range 6}, \"January\", {Broker Activity Report Range 1}, OR(@cell = \"EMORY DECATUR I\",@cell = \"EMORY DECATUR II\",@cell = \"EMORY DECATUR III\"))<\/p>

Hope this helps,<\/p>

Dave<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111186,"type":"question","name":"Dividing to Create a %","excerpt":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the formula for Smartsheet?","snippet":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the…","categoryID":322,"dateInserted":"2023-10-04T15:20:08+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T18:59:48+00:00","insertUserID":78761,"insertUser":{"userID":78761,"name":"tmkj20","url":"https:\/\/community.smartsheet.com\/profile\/tmkj20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gnPeZ7gLMjw!!EdcH89Wp7wc","dateLastActive":"2023-10-04T17:29:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3392881196,"url":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","format":"Rich","lastPost":{"discussionID":111186,"commentID":398450,"name":"Re: Dividing to Create a %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398450#Comment_398450","dateInserted":"2023-10-04T18:59:48+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T17:30:15+00:00","dateAnswered":"2023-10-04T16:17:20+00:00","acceptedAnswers":[{"commentID":398399,"body":"

You would just use<\/p>

=[Column B]@row \/ [Column A]@row <\/p>


<\/p>

EDIT: Then apply the percentage format to the column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111167,"type":"question","name":"Looking to Countif a person's name is contained in a column that allows for multiple contacts.","excerpt":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If a person's name is contained in that column and it matches the name in the column on the metrics sheet called Contact - then I want it to count…","snippet":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If…","categoryID":322,"dateInserted":"2023-10-04T02:50:21+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T13:57:49+00:00","insertUserID":131821,"insertUser":{"userID":131821,"name":"Melitta","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Melitta","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7FjR-2zeSSY!zOlQs_Tjxjo!J3yyx3hqT9S","dateLastActive":"2023-10-05T01:24:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-05T02:57:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3392817490,"url":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111167,"commentID":398349,"name":"Re: Looking to Countif a person's name is contained in a column that allows for multiple contacts.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398349#Comment_398349","dateInserted":"2023-10-04T13:57:49+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-05T02:57:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T11:48:58+00:00","dateAnswered":"2023-10-04T09:06:57+00:00","acceptedAnswers":[{"commentID":398315,"body":"

Hi @Melitta<\/a> <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row,@cell)<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Can you use OR in a SUMIFS formula

    I have a sheet full of data from a form that is sent out each month to multiple groups of people regarding multiple different properties. I have 4 quantifiable questions on the form that I have been using the following formula on to collect data. Example below =SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report…

  • Dividing to Create a %

    I would like to use the data in Column A and B to create a percentage complete by row NOT by parent/hierarchy. In Excel this would be =sum(Column B/Column A) = %. What is the formula for Smartsheet?

  • Looking to Countif a person's name is contained in a column that allows for multiple contacts.

    =COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If a person's name is contained in that column and it matches the name in the column on the metrics sheet called Contact - then I want it to count…