Find cells matching a criteria and return combined results

electrichead

Hi,

New to Smartsheet and hitting a roadblock in one of my formulas.

I have two sheets: Projects and Resources. On the Projects sheet I collect resource groups that will work on a project. Each project in a row might have either single or multiple resource groups. On the Resources sheet I have resource group names alongside the email addresses for those resource group owners. Each resource group in a row may have single or multiple email contacts.

I want to add a column in Projects that looks at the resource group in a row and returns all the email contacts.

I can do index/match, but only gives me one result. I'm not sure how to find/combine the results from all the matches. Image below and attachment with sample data.

Thanks for any help!

Email formula needed.xlsx

jmyzk_cloudsmart_jp

Hi @electrichead

Here is another solution using @cell instead of "Fruit", "Cereal", etc.

https://app.smartsheet.com/b/publish?EQBCT=1237052606d447118db82d41377b4a13

Formula:

=JOIN(COLLECT({Resource : Group Lead}, {Resource : Group}, CONTAINS(@cell, [Resource Group]@row)), ", ")

Explanation:

This formula retrieves and combines email contacts from the "Resources" sheet based on matching resource groups in the "Projects" sheet. Let's examine how each part works:

1. By using COLLECT with this setup, the formula gathers all email addresses from {Resource : Group Lead} that correspond to any matching resource groups in [Resource Group]@row.
   • COLLECT: This function gathers all matching values from a specified range based on given criteria.
   • {Resource : Group Lead}: This is the range in the "Resources" sheet where the email addresses are stored.
   • {Resource : Group}: This is the range in the "Resources" sheet where the resource group names are listed.
   • CONTAINS(@cell, [Resource Group]@row): This criterion checks if the resource group(s) listed in the current row of the "Projects" sheet (i.e., [Resource Group]@row) is present in each entry of the {Resource : Group} column.
     • @cell : Refers to each individual cell in the {Resource : Group} column as COLLECT goes through the list.
     • [Resource Group]@row: Refers to the value in the "Resource Group" column for the current row in the "Projects" sheet.
     • The CONTAINS function allows this to work with multiple resource groups listed in one cell, matching any that are present in {Resource : Group}.
2. JOIN(..., ", "):
   • After COLLECT retrieves all relevant email addresses, JOIN combines these results into a single cell.
   • The ", " parameter in JOIN specifies that each email address should be separated by a comma and space for readability.

electrichead

@jmyzk_cloudsmart_jp, I ultimately ended up using a variant of what you provided. I needed HAS because I decided to format my column for collecting Resource Group for each Project as a drop down list instead of a text/number. Here was the end formula:

=JOIN(COLLECT({Fruit Resource Groups Range 1}, {Fruit Resource Groups Range 2}, HAS([Resource Group]@row, @cell)), ", ")

Thanks for the detailed break down as well. Great for someone learning like me. It seemed that using @cell was the key for me in getting this to work and unfortunately I don't see much good documentation/examples of how this works. However, with your explanation I think I am starting to understand it.

For others learning, HAS works similar to CONTAINS. However, HAS will work with a column formatted as a drop down list, but CONTAINS will not. If you substitute them note that within the formula structure the order of "search range" and "search criteria" are reversed! Not sure why they did it this way, but it tripped me up initially.

zamirmewelldy

To find cells matching specific criteria and return combined results, we can utilize functions that search for data within a range based on defined conditions, consolidating the outputs into a single, summarized result. For example, in spreadsheet software like Excel or Google Sheets, functions such as FILTER, IF, VLOOKUP, or INDEX in combination with MATCH can locate cells that meet certain criteria and gather them in a structured format.

electrichead

Thanks for the reply zamirmewelldy, Do you have any thoughts on how to structure the formula in Smartsheet? These are common formulas, I agree, and you seem to grasp the type of problem I'm facing.

If I confused you, I only attached example data in excel format because it was easy for me to do this and it can be easily copied/pasted into a Smartsheet by someone wanting a simple dataset to work with. I'm not actually using Excel or Google sheets for this problem.

Hollie Green

Not sure how many Resource Groups you have it requires a cell reference for each group but does work. The challenges is having both of them multi select cells

=JOIN(COLLECT({Resource Group Lead Fruit}, CONTAINS("Fruit", [Resource Group]@row), 1), ",") + "," + JOIN(COLLECT({Resource Group Lead Cereal}, CONTAINS("Cereal", [Resource Group]@row), 1), ",") + "," + JOIN(COLLECT({Resource Group Lead Candy}, CONTAINS("Candy", [Resource Group]@row), 1), ",")

electrichead

Hi Hollie, I tried that formula but I couldn't get it to work for me. So I tried just using one line of it and still no luck. I had to modify it a bit to work with the my dataset.

=JOIN(COLLECT([Resource Group Lead]1:[Resource Group Lead]3, CONTAINS("Fruit", [Resource Group]@row, 1)), ",")

I get an #Incorrect argument set error.

Any thoughts why?

Michelle Choate 2

Your parentheses are in the wrong place.

=JOIN(COLLECT([Resource Group Lead]1:[Resource Group Lead]3, CONTAINS("Fruit", [Resource Group]@row), 1), ",")

jmyzk_cloudsmart_jp

Hi @electrichead

Here is another solution using @cell instead of "Fruit", "Cereal", etc.

https://app.smartsheet.com/b/publish?EQBCT=1237052606d447118db82d41377b4a13

Formula:

=JOIN(COLLECT({Resource : Group Lead}, {Resource : Group}, CONTAINS(@cell, [Resource Group]@row)), ", ")

Explanation:

This formula retrieves and combines email contacts from the "Resources" sheet based on matching resource groups in the "Projects" sheet. Let's examine how each part works:

1. By using COLLECT with this setup, the formula gathers all email addresses from {Resource : Group Lead} that correspond to any matching resource groups in [Resource Group]@row.
   • COLLECT: This function gathers all matching values from a specified range based on given criteria.
   • {Resource : Group Lead}: This is the range in the "Resources" sheet where the email addresses are stored.
   • {Resource : Group}: This is the range in the "Resources" sheet where the resource group names are listed.
   • CONTAINS(@cell, [Resource Group]@row): This criterion checks if the resource group(s) listed in the current row of the "Projects" sheet (i.e., [Resource Group]@row) is present in each entry of the {Resource : Group} column.
     • @cell : Refers to each individual cell in the {Resource : Group} column as COLLECT goes through the list.
     • [Resource Group]@row: Refers to the value in the "Resource Group" column for the current row in the "Projects" sheet.
     • The CONTAINS function allows this to work with multiple resource groups listed in one cell, matching any that are present in {Resource : Group}.
2. JOIN(..., ", "):
   • After COLLECT retrieves all relevant email addresses, JOIN combines these results into a single cell.
   • The ", " parameter in JOIN specifies that each email address should be separated by a comma and space for readability.

electrichead

@jmyzk_cloudsmart_jp, I ultimately ended up using a variant of what you provided. I needed HAS because I decided to format my column for collecting Resource Group for each Project as a drop down list instead of a text/number. Here was the end formula:

=JOIN(COLLECT({Fruit Resource Groups Range 1}, {Fruit Resource Groups Range 2}, HAS([Resource Group]@row, @cell)), ", ")

Thanks for the detailed break down as well. Great for someone learning like me. It seemed that using @cell was the key for me in getting this to work and unfortunately I don't see much good documentation/examples of how this works. However, with your explanation I think I am starting to understand it.

For others learning, HAS works similar to CONTAINS. However, HAS will work with a column formatted as a drop down list, but CONTAINS will not. If you substitute them note that within the formula structure the order of "search range" and "search criteria" are reversed! Not sure why they did it this way, but it tripped me up initially.

jmyzk_cloudsmart_jp

@electrichead

It's great to see you've found a good solution that works for your specific setup! Using HAS in place of CONTAINS accommodating the dropdown list format was a clever adjustment.😁