Index Join Collect - gather list

I want to create a formula that gathers a list of zones by identifying the area. Column A is the area, Column B are all the zones that are under each area. In the related form, the person enters Column A area and adds a zone to Column B that is under that particular area. As the list of zones builds with each form issued, I want a formula that can automate the process of filtering and identifying any areas that matches each zone and lists all those related zones in Column C.

I have tried to create the formula but not quite getting right..

=IFERROR(INDEX([Column A], ="Zone A", JOIN(COLLECT([Column B]@cell, 0), " "))

=JOIN(COLLECT([Column B], IF([Column A], HAS([Column A]@row, @cell)), "Zone A")

I will need to list the different zones in the formula so that it can match the zone data in Column B to the correct area in Column A, and list them together in Column C. Hopefully this makes sense.

Find more posts tagged with

Accepted answers

Jennifer Kurtz

@Michelle Maas — Ack - my fault; sorry! I mistyped the criteria reference in my example formula. If you're collecting values in Column B based on the value in Column A, you'll want to reference Column A in your criteria. (I edited my response above with this correction) So,

=JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column A]@row, @cell)), ", ")

And if you only wanted each value listed once:

=JOIN(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column A]@row, @cell))), ", ")

All comments

Paul Newcome

What would be the expected outputs based on the screenshot above?

Jennifer Kurtz

@Michelle Maas - Good morning!

I think you're on the right track using the JOIN(COLLECT functions together. :) However, you won't need the IF statement in there, since the COLLECT function will take care of that. Also, HAS works great if you're looking in a multi-select dropdown. If you're not using a multi-drop down, try CONTAINS instead. The syntax for using COLLECT is COLLECT(Range you're collecting, CriterionRange1, Criterion1, CriterionRange2, Criterion2…

I'm not exactly sure what you're after for Column C, but here's an example that may help:

=JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS("Zone A", @cell)), ", ")

The formula would be collecting all values in your Column B (Issues) that have a "Zone A" listed in Column A, and it would separate each Issue with a comma & a space. (I used CONTAINS in this example. If you're looking @ a multi-select drop-down, then stick with HAS! The HAS syntax is different - HAS(@cell, "Zone A")

Does that help get you started?

Michelle Maas

@Paul Newcome The outcomes is that all the zones (Column B) related to that one area (Column A) are listed in Column C.

@Jennifer Kurtz That formula works. Now, how do I tweak the formula to recognise what the Area is on that row in Column A and add to the same row's Column C the relevant zones that have been listed in all the previous rows, including what has been added on the new row, all together in one list. See below. I realise once the formula has been converted to Column Formula, it will fill all the rows with the matching zones, so the below image is to assist in further clarifying how the data comes in from the forms while we are in the process of building the list:

Jennifer Kurtz

@Michelle Maas -

Sure — you'd just want to use a row reference as part of your CONTAINS -

=JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column A]@row, @cell)), ", ")

As you've stated, this will result in a lot of repeating info on your sheet. Depending on how you/others will use this information, you could consider creating a separate sheet to collect these values. (In other words, a separate sheet would list each Zone one time, and then collect all the issues within each Zone… But of course, you'll know what works best for you or your group!)

Michelle Maas

@Jennifer Kurtz Yes, that is what we want, repeating information in Column C - it will become a full list of the zones under their designated area. I realised I had the wrong labels in the columns so have corrected these. This is a support ticket sheet where the client submits via a form any issues for a fault in a certain area and zone.

This is a huge building with lots of zones. We are wanting to build a list of zones and their related area that will become an index later on. Each time a new form is submitted, it may be the same zone as a previously reported issue, or a new zone. So in the test sheet, we have the following currently. As new zones are entered, I need to be able to add to this to the formula to continue to pick up the various zones. We are in testing phase and may be able to get a full list of the zones but first we need to prove to the client that this sheet is capable of doing this.

Area A = Zone 1, Zone 2, Zone 3, Zone 4

Area B = Zone A, Zone B, Zone C

Jennifer Kurtz

@Michelle Maas - thanks for the additional explanation/detail! I'm not quite following, but it sounds like you have a solid start on your sheet.

Your formula will "pick up" any new zones that are added via a form, provided you're using the @row reference as your criteria.

If zones or issues show up multiple times on the sheet and you only want them listed once in column C, you can include the "DISTINCT" funtion in there, too - like, =JOIN(DISTINCT(COLLECT(….

that will collect only the distinct values that meet your criteria, and join them together.

Not sure if that helps… If you're looking for additional input, let us know! Otherwise good luck!! :)

Michelle Maas

@Jennifer Kurtz the second formula returns a blank cell.

=JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column B]@row, @cell)), ", ")

I can see its not matching the relevant zones with the right areas. If I can get the formula correct and then change the column setting to "Convert to Column Formula", the final output of the data would look like the below table.

Jennifer Kurtz

=JOIN(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column A]@row, @cell)), ", ")

And if you only wanted each value listed once:

=JOIN(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], CONTAINS([Column A]@row, @cell))), ", ")

Michelle Maas

@Jennifer Kurtz fantastic, thank you for sticking with me to get this resolved. Appreciate the help. 😁

Jennifer Kurtz

Excellent- so glad you got it working! Have a great day 😊

Sign in to join the conversation!

Quick Links

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.