Feb 25 2022 12:33 PM
So I work in healthcare and we are implementing a new No-Show policy. I can pull data of all patients in our office that are no-show patients but that includes patients who do not qualify for dismissal. I can export the data to excel and there are three columns: one column with the name of the patient, another is the date of the appointment, and another column labeled "Appt Status" (which of course is No Show for everyone). Because of this setup the patients who have multiple no-shows appear in multiple rows. I would really like to filter out the data so it only displayed patients who have at least 3 no-show visits. Any help?
Feb 25 2022 12:51 PM
=UNIQUE(FILTER($A$2:$A$54,COUNTIF($A$2:$A$54,$A$2:$A$54)>=3))
Do you want a unique list of patient names with at least 3 no shows? The above formula could be what you are looking for if you work with Office 365 or 2021 or Excel online.
Feb 28 2022 08:34 AM
@OliverScheurich this works great! Is there any way that I can find out how many no-shows each of these patients have? I've been messing around with the UNIQUE/COUNT formula trying to figure it out myself
Feb 28 2022 08:54 AM
With these formulas the number of no-shows for each patient can be calculated in my spreadsheet.
=COUNTIF($A$2:$A$54,F2)
In the attached example i have this formula in cell G2.
=UNIQUE(A2:A54)
This is the formula in cell F2.
Feb 28 2022 10:13 AM
365 allows complex formulas to be built up.
= LET(
patient, SORT(UNIQUE(name)),
lastAttended, XLOOKUP(
patient,
IF(status="Attended", name),
date,
"Never attended", 0,-1),
apptCount, COUNTIFS(name,patient),
noShows, COUNTIFS(name,patient, status, "No show"),
apptRecord, CHOOSE({1,2,3,4}, patient, lastAttended, apptCount, noShows),
FILTER(apptRecord, noShows>=3))