Comparing multiple records and Deleting unnecessary ones

Howdy All,

I need help.....

I have a worksheet that contains, on average between 3000 to 4000 records which I run every month. The worksheet contain record of files submission by customers. The problem is that I have customer submitting the same file multiple times in the same day. Each time a customer submit, the system creates a submission_ID.

Column A ---> Contains Customer ID
Colomn C ---> Contains Submit Date
Column E ---> File ID

I'd like to have a script or macro to check for each record - if same Customer ID AND Same submit Date, then look in Column E for the highest ID number (the latest file submitted) and retain that record. Then Delete all records that belong to the same Customer ID which submit on the same date.

Example:

Customer ID | Submit Date | File ID
Q1 | 1/23/08 | 12056 (Delete this record)
Q1 | 1/23/08 | 12268 (Delete this record)
Q1 | 1/23/08 | 13023 (Keep this record - same customer/date, latest File)
Q1 | 1/26/08 | 15756 (Keep this record - different date)
EH | 1/26/08 | 15766 (Keep this record - different Customer ID)
MY | 1/27/08 | 16745 (Delete this record)
MY | 1/27/08 | 16875 (Keep this record - same customer/date, latest File)

Thank you very much for the help....

Much Appreciated.

CD

Back up and try

Please Login or Register  to view this content.

Howdy Jindon,

Thank you very much for the response.

I tried running the codes you had, but got "Run-Time Error 429: ActiveX Component can't create object". I researched the Internet to see what this error means and found that it has to do with MDAC from MS Office. In addition, I found that ActiveX is not supported on MS Office 2004 running on Mac.

I am using the Mac running MS office 2004 version..... Felt disappointed that I can't use your code to..... I have no clue with programming..... to make any change to make it work on Mac.

Very much appreciate it if you could help make the change....

Thank you very much.

CD