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?

Find more posts tagged with

Accepted answers

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

All comments

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

=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! 🙂

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.