Remove duplicates from list but leaving one row with highest date
 

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

Remove duplicates from list but leaving one row with highest date
 

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

wrote:

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

Remove duplicates from list but leaving one row with highest date
 

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

wrote:
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

wrote:

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

Remove duplicates from list but leaving one row with highest date
 

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


Susan wrote:
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

wrote:
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

wrote:

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

Remove duplicates from list but leaving one row with highest date
 

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

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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


Susan wrote:
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

wrote:
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

wrote:

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

Remove duplicates from list but leaving one row with highest date
 

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 wrote:
What is the format of the data in the 'records';?

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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


Susan wrote:
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

wrote:
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

wrote:

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

Remove duplicates from list but leaving one row with highest date
 

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)

wrote in message
oups.com...
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 wrote:
What is the format of the data in the 'records';?

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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


Susan wrote:
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

wrote:
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

wrote:

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