How to match clinical trials to subjects based on eligibility criteria columns?
Hello,
I have 2 sheets and I want to pull information from sheet to another sheet if certain criteria are met. The first sheet, "Slot List", is a list of clinical trials and the columns contain the study name and certain eligibility criteria patients need to meet to join the study. On the second sheet, "Trial Match", is a list of subjects and the columns contain the eligibility criteria they have. If all of the eligibility criteria in the subject's row (Trial Match sheet) is listed as the eligibility criteria in the study rows (Slot List sheet), I want to pull all potential trials names the subject could be eligible for to the "Eligible Trials" column in the "Trial Match" sheet.
I think I need to use JOIN(COLLECT, but my formulas are not working. I tried simplifying the formula to just the "Cancer Type" criteria using the formula below, but the result is blank.
=JOIN(COLLECT({Study Name}, {Cancer Type}, CONTAINS([Cancer Type]2, {Cancer Type})), ", ")
Sheet 1: "Slot List"
Sheet 2: "Trial Match"
Answers
-
Hey @S_Berkley
You're very close! Instead of using CONTAINS, since you're looking in a Multi-Select cell I would suggest using HAS instead.
In either CONTAINS or HAS, we'll want to use @cell instead of {the range again}. This is because we're looking into each individual cell of the previously stated range.
Try this:
=JOIN(COLLECT({Study Name}, {Cancer Type}, HAS(@cell, [Cancer Type]@row)), ", ")
Let me know if that worked for you.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!