+ Reply to Thread
Results 1 to 9 of 9

Using CF to find repeat patient

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    Singapore
    MS-Off Ver
    2021
    Posts
    14

    Red face Using CF to find repeat patient

    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!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Using CF to find repeat patient

    Perhaps try this as your conditional formatting formula:

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

    Beth.
    Attached Files Attached Files
    Last edited by BanginMyHeadOnMyDesk; 11-10-2019 at 03:32 AM.

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Using CF to find repeat patient

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

  4. #4
    Registered User
    Join Date
    08-12-2019
    Location
    Singapore
    MS-Off Ver
    2021
    Posts
    14

    Re: Using CF to find repeat patient

    Quote Originally Posted by BanginMyHeadOnMyDesk View Post
    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!

  5. #5
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: Using CF to find repeat patient

    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.

  6. #6
    Registered User
    Join Date
    08-12-2019
    Location
    Singapore
    MS-Off Ver
    2021
    Posts
    14

    Re: Using CF to find repeat patient

    Quote Originally Posted by BanginMyHeadOnMyDesk View Post
    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............

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,303

    Re: Using CF to find repeat patient

    Assuming the conditional formatting is for column A, try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    08-12-2019
    Location
    Singapore
    MS-Off Ver
    2021
    Posts
    14

    Re: Using CF to find repeat patient

    Quote Originally Posted by JeteMc View Post
    Assuming the conditional formatting is for column A, try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Thank you very much JeteMc! The formula works!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,303

    Re: Using CF to find repeat patient

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find the last time a patient came
    By maxkim in forum Excel General
    Replies: 2
    Last Post: 07-07-2016, 03:08 PM
  2. Replies: 1
    Last Post: 04-30-2015, 12:19 AM
  3. Replies: 5
    Last Post: 04-28-2015, 10:38 PM
  4. [SOLVED] To Find every instance of TEXT copy, repeat until no Find found
    By DadaaP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2013, 02:15 PM
  5. code to find, copy and paste until find new, then repeat
    By siddharthariver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-12-2010, 04:02 PM
  6. Repeat find and replace
    By gjcht in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2008, 07:38 PM
  7. [SOLVED] Repeat patient name for radiology charges
    By mkobulni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-17-2005, 08:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1