Find if any string from a list of strings is present in a cell
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!
Best Answers
-
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.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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)
Answers
-
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.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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)
-
Always happy to help even only by giving a hint.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!