Multiple Criteria Sorting

[Smoken55]

Hello All. My first Post here. I've searched the KB but I couldn't find what I was looking for. If someone has seen my type of issue in the KB, if you would, please let me know where.
Otherwise --
Objective: 1) Identify multiple rows (could be 2 rows or 3 rows...) that contains identical data in specific cells (ID #). 2) Compare data (from all identified rows) in specific cells that is different. 3) Eliminate the unwanted rows.

Data: I have a worksheet that contains Names, ID #'s, Descriptions, dates, status... and so on. I could have the same person (meaning: same name, same ID #, same date) but their date and/or status may differ. I would want to keep the most current row that has a status of "Successful" and remove the other records of this person from the file.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID #[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]bob jones[/TD]
[TD]123456[/TD]
[TD]Not Evaluated[/TD]
[TD]05/01/2016
[/TD]
[/TR]
[TR]
[TD]bob jones[/TD]
[TD]123456[/TD]
[TD]Successful[/TD]
[TD]05/02/2016
[/TD]
[/TR]
[TR]
[TD]bob jones[/TD]
[TD]123456[/TD]
[TD]Successful[/TD]
[TD]05/01/2016[/TD]
[/TR]
[TR]
[TD]Karen james[/TD]
[TD]654321[/TD]
[TD]Not Evaluated[/TD]
[TD]02/05/2017[/TD]
[/TR]
[TR]
[TD]Karen james[/TD]
[TD]654321[/TD]
[TD]Unsuccessful[/TD]
[TD]03/06/2017[/TD]
[/TR]
</tbody>[/TABLE]

Using the example above, I have bob 3 times (3 rows). My target is that he is "Successful" but I don't want the last instance or last row, I need the most current date. Also, I would need to do the same check for each unique person (for Karen and others as well). I have over 3000 unique people, so potentially I could have 9000 +- total rows on the worksheet to sort through. I have to do this check every week, so if I could automate this as a Macro or as a template, that would be great! I could probably figure out how to read in the file in VBA, but it's how to identify the row(s) that I need, that's what I need help with.

Environment: Windows10 Pro., Office 365

Any assistance would be greatly appreciated.

 

[Rijnsent]

Hi Smoken55,

what you could do to solve this without macros: add a column with the COUNTIFS formula.
If I take your data and put it in a worksheet on B1:E6 (headers in row 1) and put this formula in A2:
=COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,C2,$E$2:$E$6,">="&E2)

That gives me a "1" for each unique row with the maximum date for that type (on name and ID#).
Hope that helps,
Koen