Forum Discussion
How to only show Excel Data for patients with 3 or more no show visists
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?
4 Replies
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))=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.
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
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.
