Determing whether a case meets two qualifiers when one qualifier requires flexibility in determing whether the match is correct

Question

I have two tables: (1) Lists clinicians and their dates of leave, (2) Lists clinics and the dates the clinics were closed.

I would like to determine how often a clinic is closed due to a clinician's leave time (determined by clinician's leave time falling on the same date).

The tricky part is linking a clinician to their clinics. The clinics often contain only part of a clinician's name, and the clinics are pretty randomly named.

I have tried to use an if(countif )>0 function with a wildcard(Clinician) set-up around the clinician's name to denote if a clinic was closed due to a clinician being gone, but have not been successful.

Thank you in advance for any guidance you are able to provide!

Here is an example of the tables:

Leave Time versus Clinic Cancellations

Answer 1

I'm not sure of a non-VBA way to do this, so I created a function that can no doubt be improved, but I think it gets the results you'd expect given the inputs you provided.

Basically the function will take the range of dates clinics were closed and the names of the clinic (all as a single argument). The function also takes the Clinician and the Leave Date as separate arguments. The function will loop through the clinic closures and determine if the clinician in the formula is a partial match for the closed clinic on the date the clinician took leave.

Public Function Closed(rngClinic, rngClinician, rngClinicianLeave)
'function loops through each clinic and clinic close date
'to determine if the clinic name contains the clinician who took leave
'on the same date the clinic is closed.
'If so, add this as a closure and continue counting
'until all clinics have been checked.

Dim Clinic() As Variant 'This stores the range of clinic names and dates they were closed
Dim Clinician As String 'just the clinician name on the date of elave
Dim ClinicianLeave As Date ' the leave date of the clinician
Dim iCounter As Integer 'this counts the # of closures given a clinician and a leave date
Dim iClinicCount As Integer 'this is just a count of the # of loops that need to occur



With Excel.Application
    Clinic = .Transpose(rngClinic)
    Clinician = rngClinician
    ClinicianLeave = rngClinicianLeave


iCounter = 0
iClinicCount = .CountA(Clinic) / 2
For x = LBound(Clinic) To iClinicCount
    If Clinic(1, x) = ClinicianLeave And Clinic(2, x) Like "*" & Clinician & "*" Then
        iCounter = iCounter + 1
    End If
Next

End With

Closed = iCounter

End Function

Attached are screenshots of the results, both the numbers and formulas.