If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Need help with formula to weed out duplicates
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 |
#3
|
|||
|
|||
Need help with formula to weed out duplicates
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 . |
#4
|
|||
|
|||
Need help with formula to weed out duplicates
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 . |
#5
|
|||
|
|||
Need help with formula to weed out duplicates
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 . . |
Thread Tools | |
Display Modes | |
|
|