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