Using Join(Collect to collect data from multiple sheets to populate in (1) cell

I am looking to combine data from (3) sheets into one sheet.

There will be 3 columns: Team, Assignment, and Notes

The cells in the Team and Assignment columns from Sheet1, Sheet2 and Sheet3 are populated from a multi-select dropdown lists.

Notes are free type.

Example: p-id on the destination sheet is xx, Sheet1 and Sheet2 have p-id's with xx, Sheet3 does not.

Sheet1 has Team A and Team B

Sheet2 has Team A, Team B and Team C

My desired result in the destination sheet would be Team A, Team B, Team C combined together in the cell removing the duplicate team names.

Same logic would apply for the formula pulling the Assignments.

I am not sure if the Join(Collect formula would work for the Notes requirements.

TIA for your assistance/suggestions!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some sample screenshots for context?

  • DB393
    DB393 ✭✭

    Hey@Paul Newcome, I think what I need is the ability to combine multiple join(distinct(collect formulas.

    I created a helper sheet to consolidate one of the sheets that was about 80% of the lift. =JOIN(DISTINCT(COLLECT({sift}, {sifd}, [Prospect-ID]@row, {msift}, {msifd}, [Prospect-ID]@row)), CHAR(75))

    this formula works just fine, however I need aggregate the data from the other (2) sheets

    I tried:

    =JOIN(DISTINCT(COLLECT({sift}, {sifd}, [Prospect-ID]@row, {msift}, {msifd}, [Prospect-ID]@row)), CHAR(75)) + JOIN(DISTINCT(COLLECT({msift}, {msifd}, [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({tsift}, {tsifd}, [Prospect-ID]@row)), CHAR(75))

    But I got an incorrect argument error. I Am I supposed to nest them all under the collect criteria?

    Thank you for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It looks like your first COLLECT is off.


    You have range, range, criteria, range, range, criteria.

  • DB393
    DB393 ✭✭

    I thought it was range, critrange, criteria?

    Wouln't the Critrange have to reference the source sheet?

    Or do I need to change it to {Range},[ColumnName]@row,[criteria]@row.. then "+" {Range},[ColumnName]@row,[criteria]@row....etc?

  • DB393
    DB393 ✭✭

    =JOIN(DISTINCT(COLLECT({sift}, [Prospect-ID]:[Prospect-ID], [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({msift}, [Prospect-ID], [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({tsift}, [Prospect-ID], [Prospect-ID]@row)), CHAR(75))

    Unparseable error

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.

  • DB393
    DB393 ✭✭

    I see it now. thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
If your column title contains a space, it must be surrounded with square brackets. This should fix it.<\/p>

=COUNTIFS(PNS@row, 1) + COUNTIFS(EIP@row, 1) + COUNTIFS(Facilities@row, 1) + COUNTIFS([Patient Care Experience]@row, 1)<\/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"}]},{"discussionID":109375,"type":"question","name":"Calculate number of days and years from start date","excerpt":"I need to see how long staff have been with us, their tenure. I need this for any given dates, meaning \"today\". I have the start date, how do I get the days from that in years and days? Example: Start date is 06\/01\/2020, today is 08\/23\/2023 so the Tenure should be 3 years, 2 months, 22 days. I would take 3.2","snippet":"I need to see how long staff have been with us, their tenure. I need this for any given dates, meaning \"today\". I have the start date, how do I get the days from that in years and…","categoryID":322,"dateInserted":"2023-08-24T01:42:08+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T15:05:11+00:00","insertUserID":82351,"insertUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":82351,"lastUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3385732039,"url":"https:\/\/community.smartsheet.com\/discussion\/109375\/calculate-number-of-days-and-years-from-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109375\/calculate-number-of-days-and-years-from-start-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109375,"commentID":392349,"name":"Re: Calculate number of days and years from start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392349#Comment_392349","dateInserted":"2023-08-24T15:05:11+00:00","insertUserID":82351,"insertUser":{"userID":82351,"name":"Leann Gibson","url":"https:\/\/community.smartsheet.com\/profile\/Leann%20Gibson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-04\/7e\/c6\/n7ec6db531603daaefab20990ddcfdd53.jpg","dateLastActive":"2023-08-24T17:16:24+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-08-24T15:05:21+00:00","dateAnswered":"2023-08-24T15:05:11+00:00","acceptedAnswers":[{"commentID":392349,"body":"

That worked perfectly, thank you!!<\/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"}]},{"discussionID":109369,"type":"question","name":"INDEXING and Validating","excerpt":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily and may span multiple years. With that said this is where I am stumped. =INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 1}, <> \"//www.santa-greenland.com/community/discussion/109156/\"),…","snippet":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily…","categoryID":322,"dateInserted":"2023-08-23T21:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T14:04:04+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-24T15:23:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T18:14:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":48,"score":null,"hot":3385713353,"url":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","format":"Rich","lastPost":{"discussionID":109369,"commentID":392314,"name":"Re: INDEXING and Validating","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392314#Comment_392314","dateInserted":"2023-08-24T14:04:04+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T18:14:37+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-08-23T21:50:04+00:00","dateAnswered":"2023-08-23T21:43:36+00:00","acceptedAnswers":[{"commentID":392246,"body":"

@Frank Hammond<\/a> <\/p>

Your formula is close, looks like you just need commas between your criterion range and criterion.<\/p>

=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)<\/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":[]}],"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