Macro to Find Duplicate Values in Two Workbooks

Hello,

I searched through this forum and other sources but could not locate a solution that works for the purposes I am looking for and I am hoping someone hear can point me in the right direction.

I am looking for a macro that will look up for values (such as tracking IDs) in a column of one spreadsheet (current/working spreadsheet) and compare it against another excel workbook that will house all previously stored historical data (archive). If the value in the current/working spreadsheet already exists in the archived file, it should prompt there user of existence of duplicate entries in the current file and highlight the row(s) with the duplicate value in the current file.

Thanks for your help.

Hi xladept,

Thanks for your feedback. I have attached two sample files per your recommendation. The first file (current file) will be the new working document while the file file named Archive will hold historic data. As you can imagine, the historic data will contain large data set and is maintained in a stand alone workbook. The purpose of the macro is to lookup by the tracking ID to make sure there are no duplicate data entries that may have already been completed in the past. These rarely happens but wanted to add a check into the process to ensure quality control. So, the hope is that macro will highlight the duplicate value and advise the user to conduct further research into the matter. And if there are no duplicate to generate a message notifying the user that no duplicate entries have been found.

I hope that makes sense. Look forward to hear from you.

Thanks again!

Thank for writing that code xladept. I am encountering an error message "Run-time error '9': Subscript out of range" when it reaches this part of the code:

Please Login or Register  to view this content.

I have both workbooks open at the time of executing the macro with the exact file and tab names so I am not quite sure what the error is.

Thanks,

That does it Robert! Thanks to you and xladept for helping me out on this.

Just out of curiosity, is it feasible to execute the macro without requiring for the file the referenced file (Archive.xlsx) to be opened in the same Excel session?

Thanks and have a great weekend.