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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some sample screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • DB393
    DB393 ✭✭

    I see it now. thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!