Help : Maxifs with criteria of any part of the cell value separated with commas

[anish.ms]

Hi Bob,

Thanks for your time!

Currently my VBA code is finding the immediate previous paid bill date from sheet2 (refers to paid consultation bills) against each patient id in sheet1 (refers to free follow-up consultation bills) based on the criteria selected (Doctor/Speciality).
As per the rules (varies based on locations), if a patient consults a doctor/specialty (paid) for any illness and again if the patient consults within 7days for the same illness, the second consultation cannot be billed. This is what I'm checking using the code to identify any missing billing (more than 7 days gap b/w the dates). I want to modify the VBA code to also check the diagnosis codes in column G. But the problem is there are multiple diagnosis codes against each consultation.

In the below instance, my VBA code is taking the previous paid bill date as 28-May-2020. Because currently it is not checking the diagnosis codes

		Patient No.	Visit / Bill Date	Doctor	Speciality	Item	Diagnosis	Gross Amount	Discount Amount	Net Amount
Sheet 1	Follow-up Visit	45419616	05-Jun-2020	Doctor 14	EMERGENCY MEDICINE	Follow-up Consultation	K29.70, K58.9			-
Sheet 2	Paid Visit	45419616	28-May-2020	Doctor 24	EMERGENCY MEDICINE	Paid Consultation	R10.84 , K21.9			206

In the below instance, one diagnosis code is matching (G43.001), hence the date 13-Jun-2020 can be considered

		Patient No.		Visit / Bill Date	Doctor	Speciality	Item	Diagnosis	Gross Amount	Discount Amount	Net Amount
Sheet 1	Follow-up Visit	75856968		25-Jun-2020	Doctor 19	NEUROLOGY	Follow-up Consultation	G43.001, M54.81			-
Sheet 2	Paid Visit	75856968		13-Jun-2020	Doctor 35	NEUROLOGY	Paid Consultation	G43.001			311

Hope I was able explain better

Thanks in advance for your help!