Need help with formula to weed out duplicates

  • Thread starter Thread starter Meenie
  • Start date Start date
M

Meenie

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
 
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?
 
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.
 
Thanks!! That works great. I'm lost when I get to array formulas. Maybe I'll
get there someday (sigh)
Meenie
 
Back
Top