How to only show Excel Data for patients with 3 or more no show visists

%3CLINGO-SUB%20id%3D%22lingo-sub-3214985%22%20slang%3D%22en-US%22%3EHow%20to%20only%20show%20Excel%20Data%20for%20patients%20with%203%20or%20more%20no%20show%20visists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214985%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20work%20in%20healthcare%20and%20we%20are%20implementing%20a%20new%20No-Show%20policy.%26nbsp%3B%20I%20can%20pull%20data%20of%20all%20patients%20in%20our%20office%20that%20are%20no-show%20patients%20but%20that%20includes%20patients%20who%20do%20not%20qualify%20for%20dismissal.%26nbsp%3B%20I%20can%20export%20the%20data%20to%20excel%20and%20there%20are%20three%20columns%3A%20one%20column%20with%20the%20name%20of%20the%20patient%2C%20another%20is%20the%20date%20of%20the%20appointment%2C%20and%20another%20column%20labeled%20%22Appt%20Status%22%20(which%20of%20course%20is%20No%20Show%20for%20everyone).%26nbsp%3B%20Because%20of%20this%20setup%20the%20patients%20who%20have%20multiple%20no-shows%20appear%20in%20multiple%20rows.%26nbsp%3B%20I%20would%20really%20like%20to%20filter%20out%20the%20data%20so%20it%20only%20displayed%20patients%20who%20have%20at%20least%203%20no-show%20visits.%26nbsp%3B%20Any%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3214985%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3215062%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20only%20show%20Excel%20Data%20for%20patients%20with%203%20or%20more%20no%20show%20visists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318153%22%20target%3D%22_blank%22%3E%40ExcelandStuff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DUNIQUE(FILTER(%24A%242%3A%24A%2454%2CCOUNTIF(%24A%242%3A%24A%2454%2C%24A%242%3A%24A%2454)%26gt%3B%3D3))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EDo%20you%20want%20a%20unique%20list%20of%20patient%20names%20with%20at%20least%203%20no%20shows%3F%20The%20above%20formula%20could%20be%20what%20you%20are%20looking%20for%20if%20you%20work%20with%20Office%20365%20or%202021%20or%20Excel%20online.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3221825%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20only%20show%20Excel%20Data%20for%20patients%20with%203%20or%20more%20no%20show%20visists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3221825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3Bthis%20works%20great!%26nbsp%3B%20Is%20there%20any%20way%20that%20I%20can%20find%20out%20how%20many%20no-shows%20each%20of%20these%20patients%20have%3F%26nbsp%3B%20I've%20been%20messing%20around%20with%20the%20UNIQUE%2FCOUNT%20formula%20trying%20to%20figure%20it%20out%20myself%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3221884%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20only%20show%20Excel%20Data%20for%20patients%20with%203%20or%20more%20no%20show%20visists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3221884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318153%22%20target%3D%22_blank%22%3E%40ExcelandStuff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20these%20formulas%20the%20number%20of%20no-shows%20for%20each%20patient%20can%20be%20calculated%20in%20my%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIF(%24A%242%3A%24A%2454%2CF2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20the%20attached%20example%20i%20have%20this%20formula%20in%20cell%20G2.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DUNIQUE(A2%3AA54)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThis%20is%20the%20formula%20in%20cell%20F2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@ExcelandStuff 

=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.

@Quadruple_Pawn 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

@ExcelandStuff 

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.

 

@ExcelandStuff 

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))

image.png