Using CF to find repeat patient

[LeeWen]

Please help on conditional formatting formula to find repeat patients.

I need to find patients who discharged in Sep 2019 and readmitted within 30 days from the last discharge date.
Patients can be readmitted multiple times in Sep.

Thank you very much!

[BanginMyHeadOnMyDesk]

Perhaps try this as your conditional formatting formula:

=AND(MONTH(C2)=9,VLOOKUP(A2,A3:B$51,2,0)-C2<=30)

Beth.

[tim201110]

=aggregate(15,6,b3:$b$51/(a3:$a$51=a2)/(match(c2,$p$1:$q$1)=1)/(b3:$b$51>c2),1)-c2<=30

[LeeWen]

Perhaps try this as your conditional formatting formula:

=AND(MONTH(C2)=9,VLOOKUP(A2,A3:B$51,2,0)-C2<=30)

Beth.

Hi,

Thanks for the solution.

What if only the 2nd admission (readmission) required to be highlighted?

For example, same patient:
1. admission 25/8, discharge 2/9 (no need to be highlighted)
2. admission 5/9, discharge 10/9 (need to be highlighted)

Thanks in advance!

[BanginMyHeadOnMyDesk]

Maybe this?

=AND(MONTH(C2)=9,VLOOKUP(A2,A3:B$51,2,0)-C2<=30,COUNTIFS(A$2:A2,A2,C$2:C2,">="&DATE(2019,9,1),C$2:C2,"<="&DATE(2019,9,30))>1)

It's a bit complicated and there is more than likely an easier way...

Beth.

[LeeWen]

Maybe this?

=AND(MONTH(C2)=9,VLOOKUP(A2,A3:B$51,2,0)-C2<=30,COUNTIFS(A$2:A2,A2,C$2:C2,">="&DATE(2019,9,1),C$2:C2,"<="&DATE(2019,9,30))>1)

It's a bit complicated and there is more than likely an easier way...

Beth.

Thank Beth!

But the formula is not working well............

[JeteMc]

Assuming the conditional formatting is for column A, try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[LeeWen]

Assuming the conditional formatting is for column A, try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thank you very much JeteMc! The formula works!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.