How do I filter a table by partial matches using a list of values?

[kittenofd00m]

I have a list of patients with their prescribed medications in one table. In another I have a list of medications.

In the first table the prescriptions contain medication names, but they can also contain other text like dosages (10 mg, 15 mg, etc) and delivery type (tablet, cream, etc).

Here is an example of the data that I am working with.....

I need to filter the table on the left using the list of medications on the right such that the table on the right only shows rows that contain one of the medications in the list on the right.

I know that this involves partial searches but I just cannot figure out how it should be done.

 

[kittenofd00m]

I mistyped that last part and did not see a way to correct it in the original post so here is the correction.....

I need to filter the table on the left using the list of medications on the right such that the table on the right only shows rows that contain one of the medications in the list on the right.

I know that this involves partial searches but I just cannot figure out how it should be done.

That should have said.....

I need to filter the table on the left using the list of medications on the right such that the table on the left only shows rows that contain one of the medications in the list on the right.

I know that this involves partial searches but I just cannot figure out how it should be done.

 

[JEC]

Not knowing the names of your tables, you could try and run this macro

Sub jec()
 Dim jv(), ar, sq As Variant, i As Long, j As Long, x As Long
 ar = Cells(1).CurrentRegion
 sq = Cells(1, 5).CurrentRegion
 
 For i = 2 To UBound(ar)
   For j = 2 To UBound(sq)
      If InStr(1, ar(i, 2), sq(j, 1), vbTextCompare) Then
        ReDim Preserve jv(x)
        jv(x) = ar(i, 2): x = x + 1
        Exit For
      End If
   Next
 Next
 
 Cells(1, 1).CurrentRegion.AutoFilter 2, jv, 7
End Sub