JOIN/COLLECT/IF statement

Ty Hickman

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.

 

 

 

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"), ";")

Ty Hickman

Paul,

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

 

Paul Newcome

Happy to help! 

 

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.

Jon Ridpath

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

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