How to join columns based on location?

T Scott

I have a form that will be completed by leaders to indicate their availability. I can use the join function easy enough to collect responses for all weeks but I would like the join to be specific to location. For example:

Column 1 = Market

Column 2 =Week 1 Availability

Column 3 = Week 2 Availability

Column 4 = Week 3 Availability

The formula would be something like: JOIN(Column2:Column4,Market="Wisconsin". There are 14 markets and I will need to create a join for each. Right now the formula is returning #UNPARSEABLE. Any suggestions?

Genevieve P.

Hi @T Scott

The way I would do this is to add one helper column to do the first part of the formula, bringing together Columns 2 - 4, like so:

=JOIN([Column2]@row:[Column4]@row, ",")

Then once you have the three cells joined together per row, you can then have another formula with a COLLECT function right after the JOIN to filter down what you're bringing back (ex. if the Market says "Wisconsin")

=JOIN(COLLECT([Helper Column]:[Helper Column], Market:Market, "Wisconsin"), " / ")

Does that make sense? Let me know if this works for you! If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Genevieve P.

Hi @T Scott

The way I would do this is to add one helper column to do the first part of the formula, bringing together Columns 2 - 4, like so:

=JOIN([Column2]@row:[Column4]@row, ",")

Then once you have the three cells joined together per row, you can then have another formula with a COLLECT function right after the JOIN to filter down what you're bringing back (ex. if the Market says "Wisconsin")

=JOIN(COLLECT([Helper Column]:[Helper Column], Market:Market, "Wisconsin"), " / ")

Does that make sense? Let me know if this works for you! If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

T Scott

Genevieve P.,

Thank you so much! I never thought about creating a helper column and then doing the formula. It worked perfectly. Appreciate your help!😁

Genevieve P.

No problem at all! 🙂