How to match clinical trials to subjects based on eligibility criteria columns?

S_Berkley

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"

S_Berkley

So far, I am able to pull potential clinical trials for the patients if there is only 1 criterion listed in each column per study (formula copied below). I am having trouble changing the formula to allow for multiple criteria to be listed in the columns for the clinical trials. I have been trying out HAS and CONTAINS functions, but so far I am getting #incorrect argument set results.

=JOIN(COLLECT({Study Names}, {Cancer Type}, [Cancer Type]@row, {Molecular}, Molecular@row, {Prior Lines}, [Prior Lines]@row), ", ")

KPH

Hi @S_Berkley

How about

=JOIN(COLLECT({Study Name}, {Cancer Type}, HAS(@cell, [Cancer Type]@row), {Molecular}, HAS(@cell, Molecular@row), {Prior Lines}, HAS(@cell, [Prior Lines]@row)), ", ")

The three parts in bold are the changes to make this work with your multiple select dropdowns.

Sheet 1

Sheet 2

S_Berkley

So I have an equation that works, but only if the subject has a single genetic mutation. If the subject has multiple genetic mutations, the equation will not match the subject to the clinical trial unless the trial allows all mutations per eligibility criteria. I need the trial to be pulled if it contains at least one of the subject's mutations. Any advice?

Here is the equation I have: =JOIN(COLLECT({Slots}, {Cancer Type}, OR(CONTAINS([Cancer Type]@row, @cell), CONTAINS("All Solid Tumors", @cell)), {Mutations}, OR(CONTAINS(Molecular@row, @cell), CONTAINS("N/A", @cell), CONTAINS("Other", @cell)), {Prior_Lines}, OR(CONTAINS("N/A", @cell), CONTAINS([# Prior Lines of Systemic Chemotherapy]@row, @cell)), {Enrollment Status}, OR(CONTAINS("On Hold", @cell), CONTAINS("Open to Accrual", @cell))), ", ")

NicoLHC

@S_Berkley could you resolve?

S_Berkley

@NicoLHC unfortunately I was not able to resolve this issue.

NicoLHC

@S_Berkley can you share the sheet with dummy data with me it’s easier my mail is Nico.roepnack@lighthouseconsultings.com

Paul Newcome

In your screenshots of Sheet 2, [Cancer Type] is either a single select dropdown or text/number field, but based on your descriptions, it sounds like [Cancer Type] is actually a multi-select dropdown?