Remove duplicates from list but leaving one row with highest date

[annysjunkmail]

Hi Group,
I am trying to delete duplicate rows but leaving one row where it has
the highest date. For example, I have 250 records in my list but
manually removing duplicates I know that there are 175 unique records
based on most recent date. I need to do this task very often.
I have searched through previous postings but I can't find anything
that meets my needs - can somehelp?

Many thanks
Tony

 

[james.billy]

Hi Tony,

Using the advanced filter you can return unique records, goto> data>
advanced filter, then give the range you want to filter, leave the
criteria blank and (my preference) tick copy to new region then select
the cell where you want your new range to go and finally make sure you
tick "Unique records only".

If your after the highest date, could you not use the Max formula?

James

 

[Susan]

tony -

i "borrowed" this code off the internet once when i was trying to
remove duplicates...... it works but i don't understand *exactly*
how.......
xxxxxxxxxxxxxxx
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

Range("a1").Select

Application.Calculation = xlCalculationManual
Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else: Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf _
(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r
xxxxxxxxxxxxxxxxxxxxxxx
hope it helps!
susan

 

[annysjunkmail]

Thanks for both responses folks.

Both routines work equally well to give me unqiue records (I also use
the VBA routine which I got similar posting) but unfortunately they do
not delete duplicates by adding logic, i.e. by date. For example, I
have an application that has received 5 extension dates but I just need
to see the latest date. I can't seem to build the logic in where I
want to extract unqiue latest records, if you see what I mean.

I don't know how to use Max(date) in conjunction with advanced filter

Any further thoughts? Appreciated.

Tony

 

[Bob Phillips]

What is the format of the data in the 'records';?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[annysjunkmail]

The data has been imported from Access 2000.
The date field I need to filter is a standard Excel date with the
dd/mm/yyyy format.
Here is an example,

RefNo NewProjectEndDate
014755 31/10/2020
020886 16/03/2008
013276 31/12/2007
013276 30/09/2007
013276 30/06/2007
031909 31/12/2007
013699 30/06/2007
018974 30/06/2007
018974 30/01/2007
028464 30/06/2007


Thanks
Tony

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).row
For i = 1 To iLastRow
'change M1 to the cell with your test date
If Cells(i, "B").Value = Range("M1").Value Then
j = j + 1
Cells(i, "A").Resize(, 2).Copy Worksheets("Sheet2").Cells(j,
"A")
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)