Parse Duplicates by Latest Date?

[DPCpresto via OfficeKB.com]

I have a table of data that contains a column with a unique property code ID,
(say Column X) and a column that contains a date on which periodic
maintenance (with a unique name) was completed (say Column Z). This data is
pulled from the server into Access and exported to Excel, based on whatever
date parameters I specify. Most of the time, I will get some duplication of
properties that may have several dates for the same type of maintenance. What
I want to do in Excel is parse out (or delete from the list) all the
duplicate properties, leaving only the one with the latest date, and, of
course, leaving all the other properties that have only one date listed. Is
that possible with VBA?

 

[Joel]

This is not very complicated. I did it the slow method which deletesone row
at time. If you hve a lot of data I can improve the speed significantly.

The code performs a sort and then deletes the 2nd row where the ID matches
in two adjacent rows.

Sub removedups()

'sort data
LastRow = Range("X" & Rows.Count).End(xlUp).Row
Rows("1:" & LastRow).Sort _
Header:=xlYes, _
key1:=Range("X1"), _
order1:=xlAscending, _
key2:=Range("Z1"), _
order2:=xlDescending

RowCount = 2
Do While Range("X" & RowCount) <> ""
If Range("X" & RowCount) = Range("X" & (RowCount + 1)) Then
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop


End Sub

 

[RB Smissaert]

As you are working with databases the simplest and neatest way to do this is
with SQL, something along these lines:

select
id,
added_date,
term_text
from table1 t1
where
added_date =
(select max(added_date)
from table1 t2
where
t1.id = t2.id
group by id)
order by
id asc


RBS

 

[Bernd P]

Hello,

You can use my UDF Mfreq:
http://sulprobil.com/html/mfreq.html

You just need to select an area with enough rows and two columns and
to array-enter:
=Mfreq("Max",X1:X20,Z1:Z20)

For more complex conditions you can use Pstat:
http://sulprobil.com/html/pstat.html

Regards,
Bernd

 

[RB Smissaert]

Posted that SQL without testing and this should work just as well:

select
t1.id
from
table1 t1
where
t1.added_date =
(select
max(t2.added_date)
from
table1 t2 where
t1.id = t2.id)


RBS