Deleting Repeated Records based on date

**jcowley** · 07-06-2010, 12:18 PM

Hi All,

I have a spreadsheet that has over a 1000 records. Some of the customer names are repeated because they have visited more than once over the past few years.

I want to delete all of the historic data, just leaving the most recent data for each customer.

I have attached an example spreadsheet, the ones in red are the records I want to delete, which would leave just the most recent records for each customer,

Many thanks

**JBeaucaire** · 07-06-2010, 01:10 PM

1) Sort the data by Column A and then by Column B so the entries are grouped and in order by date
2) In E1, put the work "KEY"
3) In E2, put this formula and then copy down:
=IF(COUNTIF($A$1:A2,A2)=COUNTIF(A:A,A2),"Keep","Delete")

4) Click on E1
5) Turn on the Data > Filter > Autofilter
6) Use the drop down in E1 to filter for the word "Delete"
7) Highlight all the remaining visible rows and delete them
8) Turn off the Data > Filter > Autofilter to see the remaining rows.