Remove Duplicates and Find and keep the Most Recent Report

[Grimm127]

I have a data base that has over 200k rows.
They are individuals that have been termed from the company.
The issue is that from multiple reports I merged together, I have the same Employee showing up multiple times based on the fact the reports that the data came from shows him up more than one.

So, I am trying to remove the duplicate Employee and only keeping the most recent term date for the employee. So in the example below I want to keep only the second from last which is file 9 which is the most recent file. I have employees that show up only twice and some showing up 6. It varies based on the number of reports I merged into the file. Is there a formula to look at the name then id which file for me to keep or delete. Since the DB is so huge I can't think of an easier method because filters don't work and slow my computer to a crawl. I appreciate any advice. Thanks

EE_NAME EMPLOYEE_NUMBER ACTUAL_TERMINATION_DATE FILE EE COUNT FILE REF
Abbott, Costello 123456 15-Jan-16 TERMS_2015 9 1
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_01APR2016 9 3
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_06JUN2016 9 7
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_12MAY2016 9 6
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_14APR2016 9 4
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_14JUN2016 9 8
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_19APR2016 9 5
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_22JUN2016 9 9
Abbott, Costello 123456 15-JAN-16 TERMS_YTD_22MAR2016 9 2

 

[63falcondude]

What is the EE count? Is it a coincidence that it is equal to the max file rep value?

 

[Grimm127]

The EE count is the number of times that Employee shows up in the merged file. Yes the File Count will match the number of times the Employee will show up on this file. Since I created this file from several reports (9 total so far).

 

[63falcondude]

What if you create a helper column called "Delete?" (let's say in column G). G2 will have the formula

=IF(E2=F2,"No","") Auto Fill this for all of column G. This should say "No" for each column that you want to keep.

Now copy column G, paste values, find & select, go to special, blanks, ok, right click on any of the selected blanks, delete, entire row.

If it doesn't see them as blanks after pasting values, you can highlight column G, Data, Text to Columns, Finish. Then find & select...

 

[Grimm127]

What if you create a helper column called "Delete?" (let's say in column G). G2 will have the formula

=IF(E2=F2,"No","") Auto Fill this for all of column G. This should say "No" for each column that you want to keep.

Now copy column G, paste values, find & select, go to special, blanks, ok, right click on any of the selected blanks, delete, entire row.

If it doesn't see them as blanks after pasting values, you can highlight column G, Data, Text to Columns, Finish. Then find & select...

Yes, that way it Matches the Employee Count to the most recent report. I will give it a whirl. Thanks man. I had a brain fart and couldn't think of anything.

 

[63falcondude]

Glad to help. Hope it works for you.