Macro to Find Duplicate Values in Two Workbooks

**beewketu** · 09-09-2015, 02:57 PM

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.

**xladept** · 09-09-2015, 04:33 PM

Hi beewketu,

You could load a dictionary from the historical data and then just scan through the new data for a match

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**beewketu** · 09-10-2015, 04:47 PM

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!

**xladept** · 09-10-2015, 06:38 PM

Hi beewketu,

Try this:

Sub beewketu(): Dim r As Long, wa As Worksheet, wc As Worksheet
                Dim TID As Object, ID As String, D As Range
            Set wa = Workbooks("Archive").Sheets("Sheet1")
            Set TID = CreateObject("Scripting.Dictionary")
            Set wc = Workbooks("Current File").Sheets("Sheet1"):
            For r = 3 To wa.Range("A" & Rows.Count).End(xlUp).Row
            ID = Trim(wa.Range("A" & r)): TID.Item(ID) = r: Next r
            For r = 3 To wc.Range("A" & Rows.Count).End(xlUp).Row
                    ID = Trim(wc.Range("A" & r))
                    If TID.Exists(ID) Then
           If D Is Nothing Then Set D = wc.Range("A" & r) Else _
            Set D = Union(D, wc.Range("A" & r))
            End If: Next r
            
            If D Is Nothing Then
            MsgBox "No dupes exist"
            Else
            D.EntireRow.Interior.ColorIndex = 6: End If
End Sub

**beewketu** · 09-10-2015, 11:30 PM

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:

Set wa = Workbooks("Archive").Sheets("Sheet1")

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,

**Trebor76** · 09-11-2015, 12:57 AM

Hi beewketu,

The workbooks being referenced also need to have their extension included, so if the Archive workbook has macros in it, the above line would read as follows:

Set wa = Workbooks("Archive.xlsm").Sheets("Sheet1")

Regards,

Robert

**beewketu** · 09-11-2015, 09:57 AM

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.

**xladept** · 09-11-2015, 02:43 PM

Hi beewketu,

You're welcome!

Not feasible

**xladept** · 09-11-2015, 04:35 PM

Hi beewketu,

Thanks for the rep!

Macro to Find Duplicate Values in Two Workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Re: Macro to Find Duplicate Values in Two Workbooks

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to find duplicate values in different workshhet and change cell colour.

Macro VBA to comapare two sheets to find duplicate values

Find and identify duplicate data across multiple workbooks

[SOLVED] Macro to find duplicate values in a column and add the corresponding rows

How to find duplicates from two workbooks (if duplicate add new value in the list)

Macro to find duplicate values

[SOLVED] 2 workbooks - how do I find duplicate entries by comparing the two

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions