JOIN/COLLECT/IF statement

Ty Hickman
Ty Hickman ✭✭✭
edited 12/09/19 in Smartsheet Basics

Has anyone used a JOIN/COLLECT statement within an IF statement? 

Specifically, I want to join and collect responses across multiple rows where a column in the sheet has a specific value.

See the example, where I have data in a source data sheet that I'm trying to summarize on another sheet. 

Not even sure if JOIN/COLLECT is the right approach.  Looking forward to your input.

Thanks.

 

 

 

8-20-2019 1-28-41 PM.png

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    A JOIN/COLLECT statement is definitely the way to go based on the desired outcome in your screenshots.

     

    What you DON'T need is the IF statement. The COLLECT basically acts like your IF and allows you to JOIN based on specific criteria.

     

    =JOIN(COLLECT(range_you_want_to_join, criteria_range, criteria), ";")

    .

    If we want to JOIN everything in [Column B] based on [Column A] having a specific text such as the letter "A", then we would want it to look like this...

     

    =JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], "A"), ";")

    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

  • Ty Hickman
    Ty Hickman ✭✭✭

    Paul,

    That did it.  Thanks for the lesson on how to use the functions.

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    I try to walk through my thought process step by step when I can. I figure it may help put things into context and allow people to apply the concepts in general to other similar problems instead of just sitting there wondering how in the world that mess works.

    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

  • Will this work across row values in different columns as well?

  • dharberts
    dharberts ✭✭✭

    Doing a Join collect with a check box and multiple countries.....

    So Belgium would have all 3 languages on one line and Unites States would have 2