Find if any string from a list of strings is present in a cell

GyroG

Hi

I have a sheet with a column "List of Tests" that has a list of test codes, e.g. row 1 = T101, row 2 = T102, row 3 = T103; this same sheet has another column "Selected" that has a check mark to show whether or not the test was selected.

A second sheet has a column that has one or more tests listed called "Applicable Tests", e.g. row 1 = T101, T102, T103; row 2 = T102; row 3 = T103. Each cell can contain one or more test code separated by a comma.

I would like to create a column in the second sheet that flags a row if one or more tests selected from the first sheet are present in the "Applicable Tests". For example, if someone checks T101 and T103 in the first sheet, then in the second sheet I would like to have a formula that flags row 1, and row 3.

I have tried, CONTAIN, HAS and other methods but I am not able to get the desired results.

Appreciate any suggestions and help!

kowal

Hi @GyroG,

Have you considered using in the 2nd sheet the index/match function just to grab the data form the first sheet?

Once the data is in the second sheet you can use the CONTAIN / HAS formula to distinguish if the tests have been completed. It's extra columns in the 2nd sheet but you can always hide them.

GyroG

Hi Kowal,

Thanks for the tip. I was able to solve it finally. I created a helper column in the main sheet using an IF statement to repeat every selected test and replace it with something random like "XYZ" if it was not selected. This was Test List Range 3.

On the second sheet, I was able to use Index/Join/Collect/Contains to list all matches from the main sheet with each row. Each row in Applicable Tests had one or more tests. If one or more tests from Test List Range 3 were found in the list of tests in the specific cell of the row, it would list all those found.

I hope someone else can find this helpful.

=INDEX(JOIN(COLLECT({Test List Range 3}, {Test List Range 3}, CONTAINS(@cell, [Applicable Tests]@row)), "; "), 1)

kowal

Hi @GyroG,

Have you considered using in the 2nd sheet the index/match function just to grab the data form the first sheet?

Once the data is in the second sheet you can use the CONTAIN / HAS formula to distinguish if the tests have been completed. It's extra columns in the 2nd sheet but you can always hide them.

GyroG

Hi Kowal,

Thanks for the tip. I was able to solve it finally. I created a helper column in the main sheet using an IF statement to repeat every selected test and replace it with something random like "XYZ" if it was not selected. This was Test List Range 3.

On the second sheet, I was able to use Index/Join/Collect/Contains to list all matches from the main sheet with each row. Each row in Applicable Tests had one or more tests. If one or more tests from Test List Range 3 were found in the list of tests in the specific cell of the row, it would list all those found.

I hope someone else can find this helpful.

=INDEX(JOIN(COLLECT({Test List Range 3}, {Test List Range 3}, CONTAINS(@cell, [Applicable Tests]@row)), "; "), 1)

kowal

Always happy to help even only by giving a hint.