How to join columns based on location?

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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    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.
    Genevieve P. Employee Admin

    No problem at all! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!