Keep last records based on conditions, VBA or formula

**Lifeseeker** · 04-05-2013, 09:30 PM

Hi there,

I have a simple test file that I am trying to put a formula in to run, but i have not been successful. I have attached the file.

Basically, here is what I want to do:

1) Going down each record in column A, if the same patient(if patient ID is the same, then it's the same patient) has the same intake date. If the intake date is the same, but the admission date is different, then I ONLY want the first occurrence of the record and delete the other ones.
2) Looking at each patient ID in column A. If the same patient has the different intake date, but their admission date is the same, then I want ONLY the last occurrence of the record and delete the other ones.

I was trying to use a formula for it to work, but I have not been successful. It seems that I am always half way there.

Any help is appreciated.

Thanks

**benishiryo** · 04-05-2013, 11:41 PM

hi Lifeseeker. your sample should include 3 scenarios:
1) Same ID, same Intake, different admission
2) Same ID, different Intake, same admission
3) Same ID, different Intake, different admission

and tell us which should be kept or deleted by manually typing in column E. i saw only 1 eg of (2) & the rest are (3). but since your formula in column E is not working like you expect, i'm not sure what should the answers be. this formula here will:
1) keep 1st entry of (1), delete the rest
2) keep last entry of (2), delete the rest
3) delete all of (3)

Formula:

=IF(OR(AND(COUNTIFS($A$2:$A$174,A2,$B$2:$B$174,B2)>1,MATCH(A2&B2,INDEX($A$1:$A$174&$B$1:$B$174,),0)=ROW()),AND(COUNTIFS($A$2:$A$174,A2,$C$2:$C$174,C2)>1,LOOKUP(2,1/(($A$2:$A$174=A2)*($C$2:$C$174=C2)),ROW($A$2:$A$174))=ROW())),"keep","Delete")

this will keep all of (3):

Formula:

=IF(OR(AND(COUNTIFS($A$2:$A$174,A2,$B$2:$B$174,B2)>1,MATCH(A2&B2,INDEX($A$1:$A$174&$B$1:$B$174,),0)=ROW()),AND(COUNTIFS($A$2:$A$174,A2,$C$2:$C$174,C2)>1,LOOKUP(2,1/(($A$2:$A$174=A2)*($C$2:$C$174=C2)),ROW($A$2:$A$174))=ROW()),COUNTIFS($A$2:$A$174,A2,$B$2:$B$174,B2)+COUNTIFS($A$2:$A$174,A2,$C$2:$C$174,C2)=2),"keep","Delete")

**Lifeseeker** · 04-08-2013, 12:49 PM

Hi,

I am attaching an updated spreadsheet. It contains all three kinds of examples and what I would like to do.

**bebo021999** · 04-08-2013, 01:18 PM

May be:

=IF(A2=A1,IF(B2<>B1,"keep","delete"),IF(A2=A3,IF(B2=B3,"keep","delete"),"keep"))

**Lifeseeker** · 04-08-2013, 01:45 PM

Hi, it works, but what if there are more than two patients that have the same ID? I am attaching the file with update.

For example, if you filter on ID for 9621 and 1680, you will see what I mean. I also added in a comparison in one of the nested ifs statement because I think we need to look at both intake date and admission date for each patient ID.

Thanks