Consulting

Results 1 to 10 of 10

Thread: Get Unique days per MD per patient

  1. #1
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location

    Get Unique days per MD per patient

    Hi all:

    Enclosed Excel file where I find the unique patients per MD using this array formula (enter has array):

    =SUM(IF(FREQUENCY(IF($B$2:$B$7=$E2,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1),1))
    Now I am trying to get the unique stay days per patient per MD using this array (which not work - result #value!):

    =SUM(IF(FREQUENCY(IF(AND($A$2:$A$7=$F2,$B$2:$B$7=$E2),MATCH($C$2:$C$7,$C$2:$C$7,0)),ROW($C$2:$C$7)-ROW($C$2)+1),1))
    Please see enclosed Excel 2010 file to see if you can help me to correct this array enter formula.

    Thanks

    Victor
    Attached Files Attached Files

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Can you repost your sample showing full layout and non-working formula.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Hi:

    Please see post with enclosed sample array enter formula.

    Thanks

    Victor

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    What defines a unique stay day? For arada, there are 2 patients with 57 days total, but as there are no days I cannot see what unique constitutes.

    Extend your example to duplicate a patient for one particular MD and tell us what you expect to get.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    xld

    Enclosed (unique md2.xlsx) extended example to duplicate a patient for one particular MD and what I expect to get.

    Thanks for the help.

    Victor
    Attached Files Attached Files

  6. #6
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    What defines a unique stay day?

    Do not repeat same patient for MD. For example
    X patient A MD 2 days
    X patient A MD 2 days

    The unique days is 2 days, since is the same patient for the MD and the patient stay is only 2 days total not 4 in the hospital. Is repeat since this patient has 2 different laboratories not shown in the data base.

  7. #7
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Correction if file name columns header in J1

    Changes: Should say "Correct result for column I" instead of "Correct result for column H"


    Thanks

    Victor

  8. #8
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    744
    Location
    See if this works on the whole sheet as it seems to work with the data you gave. ARRAY ENTERED:
    =SUM(($B$2:$B$17=$E2)*$C$2:$C$17*(ROW($A$2:$A$17)=(MATCH($A$2:$A$17&$B$2:$B$17,$A$2:$A$17&$B$2:$B$17,0)+1)))
    I am attaching the worksheet where you will find this formula in Col I.
    Attached Files Attached Files
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  9. #9
    VBAX Regular
    Joined
    Dec 2008
    Posts
    85
    Location
    Hi Shrivallabha

    Brilliant use of the concatenation ranges in the match function.

    That is the solution I was looking for.

    Bye, until next time.

    Thanks all for the interest in help me.

    Happy holidays to all!

    Victor

  10. #10
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    744
    Location
    Thanks to this forum and MrExcel where I have seen great formula solutions being provided. Its all taken from here.
    Wish you happy holidays. Have fun
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

Posting Permissions

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