JOIN COLLECT with OR criterion?

LizTo · 02/07/23

Hello community,

I am trying to put together a JOIN COLLECT formula with a criterion whose range is a multi-select dropdown menu. Right now the formula is working when only one is selected from the menu.

=JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, Regions@row), ", ")

How can I adjust the formula to pull from the source sheet when any of the regions are selected?

For example, right now it will work when the criterion range has Region X selected, but not when it has both Region X and Region Y selected. I want it to populate the cell from the source sheet with results from both Region X and Region Y. In the source sheet, the Region column is a single-select dropdown.

Thank you for any guidance you can provide!

Samuel Mueller · 03/23/23

@LizTo Okay, try this...

make it a multi select drop down to remove duplicates.

=IF(HAS(Positions@row, "Communications"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Communications")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "ERC"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "ERC")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Finance"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Finance")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "HR"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "HR")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Logistics"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Logistics")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Planning"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Planning")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Programs"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Programs")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "REC"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "REC")), char(10)), "")

+char(10)+IF(HAS(Positions@row, "Safety & Security"), JOIN(COLLECT({ERT Emails}, {status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(@cell, "Safety & Security")), char(10)), "")

Samuel Mueller · 03/27/23

@LizTo any luck?

Samuel Mueller · 02/07/23

@LizTo try adding a "contains" method to your formula

=JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, contains(Regions@row, @cell)), ", ")

Let me know if that works.

LizTo · 02/07/23

Hi @Samuel Mueller , thank you so much. Unfortunately, that didn't work for me. It's still only pulling results if I have only one region selected, but not if multiple are selected.

LizTo · 02/07/23

@Samuel Mueller I think I got it to work using HAS instead of CONTAINS.

Samuel Mueller · 02/07/23

@LizTo sounds good! the contains function should have worked that's odd but I'm glad you found a solution!

Samuel Mueller · 02/07/23

Never mind I think I was thinking of your solution backwards. I thought {Region} was the multi select column

LizTo · 02/07/23

@Samuel Mueller My original question could have been clearer. And your response still led me to a working solution, so thank you!

LizTo · 03/20/23

@Samuel Mueller

I'm still having a hard time with this formula and can't figure it out. In the source sheet, Region is a single-select and Positions is a multi-select. In the sheet where I want to use this formula, Regions is a multi-select and Positions is also a multi select.

This is the formula that I am currently using,

=JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, HAS(Positions@row, @cell)), ", ")

but the problem is that it's only showing people who have all the same exact ERT Functions selected, whereas I want them to show up if there is any overlap at all between what they have selected in the source sheet and what is indicated in the second sheet.

For example, Sample Person might be able to do the functions of Communications, Planning, or Logistics.

We might be looking for people who can do Programs, Logistics, OR Security. In that case, Sample Person should show up since Logistics is a match. Instead, the formula is only pulling people who can do Programs, Logistics, AND Security.

I've tried using CONTAINS instead and it's not working either.

Is this possible?

Samuel Mueller · 03/21/23

@LizTo How many ERT functions are we talking?

I'm asking because you may need a series of formulas. something like...

=JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Communications", @cell)), ", ") + ", " + JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Planning", @cell)), ", ")

LizTo · 03/22/23

Hi @Samuel Mueller ,

There are nine different options in that dropdown menu:

Communications

ERC

Finance

HR

Logistics

Planning

Programs

REC

Safety & Security

But in each row, it's unlikely that all nine will be selected. The selected options will vary per row.

Would that still work using the series of formulas like in your example above?

Samuel Mueller · 03/22/23

@LizTo Copy and paste all the below into a cell and see what you get. Hopefully no errors :)

=JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Communications", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Planning", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("ERC", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Finance", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("HR", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Logistics", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Programs", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("REC", @cell)), ", ") + ", "

+ JOIN(COLLECT({ERT Emails}, {Status}, "Active", {Region}, HAS(Regions@row, @cell), {ERT Functions}, has("Safety & Security", @cell)), ", ")

LizTo · 03/22/23

@Samuel Mueller

Thank you!

I've been comparing the results I get from that formula to using a filter on the source sheet to see if the responses match, which they should.

Unfortunately, it's giving me some extra names that shouldn't be there and is missing some other names. I haven't figured out the pattern after playing with several examples so I don't know exactly what the problem is yet.

Samuel Mueller · 03/22/23

Can you send some screenshots?

LizTo · 03/22/23

@Samuel Mueller

Here are the relevant columns from the source sheet:

I am trying to join collect the Email(s) from this source sheet (colorful columns above), based on the Status, Region, and ERT Functions, into this new sheet (white screenshot below). The "ERT contacts" column is where the formula is going and it's pulling in the email address from the source sheet. I want to end up with a list of email addresses that are filtered based on the status (always Active), Regions, and Positions. I don't know if this is just a question of the formula, or if I need to structure the data or columns in a different way, too.

Samuel Mueller · 03/23/23

...

Samuel Mueller · 03/23/23