Delete Entire Row Based on Multiple Criteria

[mcmullenrich]

I have a spreadsheet with about 8,000 lines with patient data. It is sorted by Patient Name (column B), then by Visit Date (column G). I need to delete all rows where a single patient does not have any 2013 dates and keep all rows where a patient has at least one 2013 date. For example:

Joe Smith 2/1/2012
Joe Smith 4/1/2012

I need to delete both rows. But if

Steve Johnson 12/1/2012
Steve Johnson 1/31/2013

I need to keep both rows.

I have found some answers about deleting entire rows based on one criteria like a cell value, but nothing addresses this exactly.

Any help would be greatly appreciated.

 

[oldbrewer]

[TABLE="width: 420"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Joe Smith[/TD]
[TD]02-Jan-12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD]04-Jan-12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve Johnson[/TD]
[TD]12-Jan-12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve Johnson[/TD]
[TD]31-Jan-13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve Johnson[/TD]
[TD]05-Feb-13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]in C1 =IF(YEAR(B1)=2013,1,0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]in D1 =SUMPRODUCT(($A$1:$A$4=A1)*($C$1:$C$4))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]you only now need sort your preadsheet by column D descending[/TD]
[/TR]
[TR]
[TD="colspan: 3"]then delete all bottom records with 0 in column D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mcmullenrich]

So there is actually another wrinkle to this. In Column A there is a Procedure Code. So to update the question:

I have a spreadsheet with about 8,000 lines with patient data. It is sorted by Patient Name (column B), then by Procedure Code (Column A) then by Visit Date (column G). I need to delete all rows where a single patient and procedure code does not have any 2013 dates and keep all rows where a patient has at least one 2013 date. For example:

E0431 Joe Smith 2/1/2012
E0431 Joe Smith 4/1/2012

I need to delete both rows.

But if

E0431 Steve Johnson 12/1/2012
E0431 Steve Johnson 1/31/2013

I need to keep both rows.

Also, if

E0431 Sally Jones 1/1/2012
E0431 Sally Jones 2/15/2013
E1390 Sally Jones 8/15/2012
E1390 Sally Jones 10/1/2012

I need to keep the first two rows but delete the last two (since that group has only 2012 dates).

I have found some answers about deleting entire rows based on one criteria like a cell value, but nothing addresses this exactly.

Any help would be greatly appreciated.

 

[oldbrewer]

use my approach - but first concatenate E0431 with Sally Jones - etc etc
un-concatenate them at the end