PDA

View Full Version : Solved: Get Unique days per MD per patient



Victor
12-20-2011, 11:52 AM
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

mdmackillop
12-20-2011, 12:09 PM
Can you repost your sample showing full layout and non-working formula.

Victor
12-20-2011, 12:52 PM
Hi:

Please see post with enclosed sample array enter formula.

Thanks

Victor

Bob Phillips
12-21-2011, 03:26 AM
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.

Victor
12-21-2011, 07:38 AM
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

Victor
12-21-2011, 10:15 AM
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.

Victor
12-21-2011, 04:11 PM
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

shrivallabha
12-24-2011, 02:09 AM
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.

Victor
12-24-2011, 12:35 PM
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

shrivallabha
12-24-2011, 09:50 PM
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 :beerchug: