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: