Get Unique days per MD per patient

[Victor]

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]

Can you repost your sample showing full layout and non-working formula.

[Victor]

Hi:

Please see post with enclosed sample array enter formula.

Thanks

Victor

[Bob Phillips]

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]

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]

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]

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]

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]

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]

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: