Need help with formula to weed out duplicates

Excel 2003
I receive information regarding patients in a hospital setting, the patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per month.
(# of yes answers divided by the total number of pts) The problem is that a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together successfully.
thanks, Meenie

It's not real clear what you want to do.

Do you want to count Yes and No for unique patients?

P1...Y
P1...Y
P2...N
P2...Y
P3...N
P4...Y
P4...Y

Where unique "Yes" would = 3 and unique "No" would = 2

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Excel 2003
I receive information regarding patients in a hospital setting, the
patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per
month.
(# of yes answers divided by the total number of pts) The problem is that
a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together
successfully.
thanks, Meenie

Yes, that's what I want to do.
I have the med rec numbers in column B and the yes no answers in several
other columns (would only calculate for one column at a time) but not sure
how to make a formula that looks at the med rec number, dismisses duplicates,
and counts the "yes" answers in another column.
Is that do-able?

"T. Valko" wrote:

It's not real clear what you want to do.

Do you want to count Yes and No for unique patients?

P1...Y
P1...Y
P2...N
P2...Y
P3...N
P4...Y
P4...Y

Where unique "Yes" would = 3 and unique "No" would = 2

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Excel 2003
I receive information regarding patients in a hospital setting, the
patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per
month.
(# of yes answers divided by the total number of pts) The problem is that
a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together
successfully.
thanks, Meenie



.

Try these array formulas** :

Based on the sample data I posted. Assumes no empty cells in the patient ID
column.

For unique Yes

=SUM(IF(FREQUENCY(IF(B2:B8="Yes",MATCH(A2:A8,A2:A8 ,0)),ROW(A2:A8)-ROW(A2)+1),1))

For unique No:

=SUM(IF(FREQUENCY(IF(B2:B8="No",MATCH(A2:A8,A2:A8, 0)),ROW(A2:A8)-ROW(A2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Yes, that's what I want to do.
I have the med rec numbers in column B and the yes no answers in several
other columns (would only calculate for one column at a time) but not sure
how to make a formula that looks at the med rec number, dismisses
duplicates,
and counts the "yes" answers in another column.
Is that do-able?

"T. Valko" wrote:

It's not real clear what you want to do.

Do you want to count Yes and No for unique patients?

P1...Y
P1...Y
P2...N
P2...Y
P3...N
P4...Y
P4...Y

Where unique "Yes" would = 3 and unique "No" would = 2

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Excel 2003
I receive information regarding patients in a hospital setting, the
patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per
month.
(# of yes answers divided by the total number of pts) The problem is
that
a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together
successfully.
thanks, Meenie



.

Thanks!! That works great. I'm lost when I get to array formulas. Maybe I'll
get there someday (sigh)
Meenie

"T. Valko" wrote:

Try these array formulas** :

Based on the sample data I posted. Assumes no empty cells in the patient ID
column.

For unique Yes

=SUM(IF(FREQUENCY(IF(B2:B8="Yes",MATCH(A2:A8,A2:A8 ,0)),ROW(A2:A8)-ROW(A2)+1),1))

For unique No:

=SUM(IF(FREQUENCY(IF(B2:B8="No",MATCH(A2:A8,A2:A8, 0)),ROW(A2:A8)-ROW(A2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Yes, that's what I want to do.
I have the med rec numbers in column B and the yes no answers in several
other columns (would only calculate for one column at a time) but not sure
how to make a formula that looks at the med rec number, dismisses
duplicates,
and counts the "yes" answers in another column.
Is that do-able?

"T. Valko" wrote:

It's not real clear what you want to do.

Do you want to count Yes and No for unique patients?

P1...Y
P1...Y
P2...N
P2...Y
P3...N
P4...Y
P4...Y

Where unique "Yes" would = 3 and unique "No" would = 2

--
Biff
Microsoft Excel MVP


"Meenie" wrote in message
...
Excel 2003
I receive information regarding patients in a hospital setting, the
patient
being
identified by Medical Record Number.
I need to know the number of pts on ventilators in a month, then the
compliance
to several different tasks/processes performed on those patients per
month.
(# of yes answers divided by the total number of pts) The problem is
that
a
lot of the data received is on the same pt, repeated on different days.
I have a formula to disregard the duplicate med rec numbers:
=sumproduct((range"")/countif(range,range&""))
this works fine to count the number of patients for the month without
counting duplicates.
How can I count the number of yes/no answers in other columns without
counting the duplicates? I don't know how to put that together
successfully.
thanks, Meenie



.



.