Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Duplicates by Latest Date?
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? -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200907/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Duplicates by Latest Date?
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 "DPCpresto via OfficeKB.com" wrote: 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? -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200907/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Duplicates by Latest Date?
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 "DPCpresto via OfficeKB.com" <u44264@uwe wrote in message news:993bb85d2fe66@uwe... 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? -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200907/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Duplicates by Latest Date?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse Duplicates by Latest Date?
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 "RB Smissaert" wrote in message ... 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 "DPCpresto via OfficeKB.com" <u44264@uwe wrote in message news:993bb85d2fe66@uwe... 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? -- DPCpresto Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200907/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove duplicates, keeping latest dated record | Excel Programming | |||
Filter latest date from multiple date entries | Excel Worksheet Functions | |||
X axis date - display beyond latest date. | Charts and Charting in Excel | |||
parse comma separated text to multiple cells and remove duplicates | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions |