Delete Duplicate records but keep 1 record with most recent date

Hello!

I have a table that contains data from columns A:H.

Column B is a Date Field (Short Date Format= mm/dd/yyyy)
Column C is an alpha numeric field (Text Format)

There can be duplicate values in column C, but each record will have a different date. For Example, let's say I have the following records:

Row # Column B Column C

1 03/25/2013 11030
2 05/07/2013 11030
3 07/01/2013 11030

In this example, I want to only keep Row #3 as this record has the most recent date. The number "11030" has been duplicated 3 times. So my criteria for deleting duplicates is to keep the duplicated record which has the most recent date and this code needs to cycle through the data set to find the duplicates and ONLY keep the most recent date. Something to keep in mind while developing this code: I have seen 2-6 duplicate records. Please help! I do not want to have to do this manually by hand as there's thousands of records to go through! Any help will be greatly appreciated.

Hi,

Did you try to use Remove duplicate function?

you can try to use this step
- sort the whole data by the date from Z-A ( sort newest to oldest )
- Block all the data and klik remove duplicates
- Uncheck all the tick box and only check the column that contain duplicates

It will keep the unique value with only recent dates.

I just tried this suggestion and the Remove Duplicates Function does keep the Unique Value BUT NOT WITH THE RECENT DATES.

Hi, the options I have on the sort is A-Z. So the oldest is the first record and the newest date is the last record.

Here is the data. The yellow rows are the most current "status" date. I want to keep those rows and delete the older dates.

Capture.PNG

Bevg, try this "cheat". Sort your data like this: First level = Column C A-Z. Second level = Column B newest to oldest. Now, based on your sample data above, in cell D2 put formula "=IF(D2<>D1,1,0)". Now, just filter for the 1's.

Hi,

GSNIDOW, Thanks for the "cheat". I love any short cut but this one only gave me one of my yellow rows and I need all three after the "Remove duplicates" function.

attached is a file so that you can try it out for yourself.

---

Puzzle.xls

Hi stibay,

Thanks a tons for the code! I'm having difficulty with it. I changed the code so that it compares the rows in column A and compares the rows in column C "if matches" to previous row then

Compare the "Status Date" if if date is greater than previous date delete the previous date (this will keep the most current date).

attached is the file. I did sort the data code=level 1, name level 2, rcvd date=level 3, status date=level 4

Puzzle.xls

I love your tag line "Just a guy trying to make work stuff easier"

Hey I tried your new suggestion and a "1" did not appear on my yellow rows. bummed.

I did sort the data code=level 1, name level 2, rcvd date=level 3, status date=level 4

The column Status Date is the most recent date I need to keep.

AWESOME GREG! It works!

Thanks for the heads up on the data types. The system I'm migrating data into is requiring all dates to be MMDDYYYY this format is not working with access nor excel.

This was a quick short cut as well. I will definitely use this method again.

Looks like you won the competition and now are crowned King of the spreadsheet!!! Have a good day!

Hi All
I am facing problem the last row function is not calculating as such
you can see the codes below

ActiveSheet.Range("A3:A" & lastRow).RemoveDuplicates Columns:=1, Header:=xlYes
Sheets("Daily PSA").Select
Dim last As Long
With Sheets("Fill The Data")
last = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("B4:B" & last).FormulaR1C1 = "=COUNTIFS('Fill The Data'!C19,""HIT"",'Fill The Data'!C3,'Daily PSA'!RC[-1])/COUNTIF('Fill The Data'!C3,'Daily PSA'!RC[-1])"

The last formula is pasted in all the cells which were present in Col A before removing the duplicates. Please correct the code if possible