Filter duplicates by multiple criteria

Hello Everyone,

Using Excel 2003. I don't use excel often but have gone through a few
classes so I kind of have an idea what is possible but not a clue how to do
what I want.

My data is a list of patients sent from an access database with a list of
patients on ventilator therapy. My first goal is to show only the unique
records but uniqness is based on a couple of things.

My duplicates come when a patient has been moved from room to room. I need
to keep only the record that is the last location when the patient was on
the ventilator.

I have been doing this maually but would like to do it with a filter if it's
possible. This is how I figure it out which records to delete.

I sort by Admit number, then by ThpyStartDtTm, then by ThpyEndDtTm. If there
are duplicate records and the Start Date and End Dates are the same I have
to delete all but one record. If the start dates and end dates are a
different time frame, I keep both records because this is 2 seperate vent
cases. The way I determine which record/records to delete is to look at the
PtLocEndDtTm (Patient location). I need to keep the record for the last
location the patient was on this therapy and delete the others.

Below is an example of a record I need to filter and delete the 2
duplicates.

Fields are Pt Number, Pt. Location, PtLocEndDtTm, ThpyType, ThpyStDtTm,
ThpyEndDtTm.

1234 4BW 01/08/09 14:00 Vent 01/04/09 18:35 01/06/09 08:30
1234 ICU 01/04/09 18:30 Vent 01/04/09 18:35 01/06/09
08:30
1234 NSI 01/06/09 10:00 Vent 01/04/09 18:35 01/06/09
08:30

In this case I would delete record 1 and 2 because the vent ended when the
patient was in NSI.

Hope this can be done and I can do it because it will save me about 3 hours
a month of manual filtering.

Thanks,
Linda

Assuming your data is in rows 1-100
Enter the following in D1 and copy down to D100
=SUMPRODUCT(--(A1:A$100=A1),--(B1:B$100=B1),--(C1:C$100=C1))

This will give you a number showing how many times the combination of values
in Col A, B and C in that row occurs from that row till row 100...
So if a particular combination occurs 3 times it will show 3, 2, 1 against
the three rows with 1 against the last row...

You can adjust A to Admit number, B to ThpyStartDtTm and C to ThpyEndDtTm

The last row for the unique combination of Admit number - ThpyStartDtTm
-ThpyEndDtTm will show 1, all other rows will show a number more than 1
You can filter on 1 and delete those rows

Hope this makes sense...
This will not work if ThpyStartDtTm and ThpyEndDtTm have a time stamp too...
in that case all your rows will be unique :-)
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Linda (RQ)" wrote:

Hello Everyone,

Using Excel 2003. I don't use excel often but have gone through a few
classes so I kind of have an idea what is possible but not a clue how to do
what I want.

My data is a list of patients sent from an access database with a list of
patients on ventilator therapy. My first goal is to show only the unique
records but uniqness is based on a couple of things.

My duplicates come when a patient has been moved from room to room. I need
to keep only the record that is the last location when the patient was on
the ventilator.

I have been doing this maually but would like to do it with a filter if it's
possible. This is how I figure it out which records to delete.

I sort by Admit number, then by ThpyStartDtTm, then by ThpyEndDtTm. If there
are duplicate records and the Start Date and End Dates are the same I have
to delete all but one record. If the start dates and end dates are a
different time frame, I keep both records because this is 2 seperate vent
cases. The way I determine which record/records to delete is to look at the
PtLocEndDtTm (Patient location). I need to keep the record for the last
location the patient was on this therapy and delete the others.

Below is an example of a record I need to filter and delete the 2
duplicates.

Fields are Pt Number, Pt. Location, PtLocEndDtTm, ThpyType, ThpyStDtTm,
ThpyEndDtTm.

1234 4BW 01/08/09 14:00 Vent 01/04/09 18:35 01/06/09 08:30
1234 ICU 01/04/09 18:30 Vent 01/04/09 18:35 01/06/09
08:30
1234 NSI 01/06/09 10:00 Vent 01/04/09 18:35 01/06/09
08:30

In this case I would delete record 1 and 2 because the vent ended when the
patient was in NSI.

Hope this can be done and I can do it because it will save me about 3 hours
a month of manual filtering.

Thanks,
Linda