Find Duplicates in two different Worksheets in MS Excel

Question during a training session in a telecom company: We are given csv which we import into an Excel worksheet. We already have data of similar type in another worksheet in the same workbook. Now we need to find the duplicates in both the worksheets. Sometimes we have names and sometimes we have some numbers, etc. The data is in different worksheets in the same workbook but in different columns. What is a good solution? We have used the following VBA code (macro) with a ‘for loop’ to solve this problem.

Sub findDuplicates()
‘ code to find duplicates in 2 different worksheets
Dim rng1, rng2, cell1, cell2 As Range
‘ 4 ranges have been defined
Set rng1 = Worksheets(“Sheet1”).Range(“B:B”)
‘rng1 defines the existing data in column B and worksheet1
Set rng2 = Worksheets(“Sheet2”).Range(“D:D”)
‘rng2 defines the imported data in column D and worksheet2
For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For
‘check for empty rows. If true then exit the program
For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1.Value = cell2.Value Then
‘compare data in cell1 and cell2 and then format if they have equal values.
cell1.Font.Bold = True
cell1.Font.ColorIndex = 2
cell1.Interior.ColorIndex = 3
cell1.Interior.Pattern = xlSolid
cell2.Font.Bold = True
cell2.Font.ColorIndex = 2 cell2.Interior.ColorIndex = 3
cell2.Interior.Pattern = xlSolid
End If
‘run the looping process
Next cell2
Next cell1
End Sub
Watch the video below to see the Excel VBA code or macro in action:

10 thoughts on “Find Duplicates in two different Worksheets in MS Excel”

1. hakim

Hi,

I have to compare two tables for my job. There are datas that match in both tables, i have to find the ones that dont match and highlight them. I would like to know if its possible to rather than changing the color of the cells, just replace the text inside the cell ? In my job i have to compare two list of employees and sort out the ones that are booked for a training and the ones that are not booked. The idea is to replace the duplicate cells by “Booked” and the one that are not duplicate by “Not Booked”.

2. hakim

and what code i have to use if i want to delete the duplicates rather than color them please

3. Jeff R.

Is there a way to have 19 worksheets (in one workbook) get sorted and checked for duplicates on each worksheet? I have the sort working, my check for duplicates only work on the first worksheet, not any of the others. using subroutines to sort worksheets then using another set of subroutines to check for duplicates. thanks,

4. Fiona West

Hi,

Thank you for the code. I have tried running this macro and it causes my excel workbook to almost come to a halt. I do not have anything else running other than this internet page. I’m trying to learn VBA so after surfing the net I came across:

Application.ScreenUpdating = False
Application.EnableEvents = False

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

In an attempt to try and speed up the process, but no luck. Can you offer any further advice? the macros I have run in the past have all finished within a few minutes max.

Thank you,

Fiona

5. Suresh

Hello Sir,
The website you created is very useful for me.
My question to you is when I compare two different worksheets of data , I want to generate new files which shows new data and missing data. Please let me how we do using Macros.

Thank you

Regards
Suresh

6. anas

How about hightlight a records means a highlighht duplicate rows instead just one cell in two worksheets.

7. Dexter

The code works very well for 2 worksheets. Thank you very much, It was very helpful.
But I also have a workbook with about 10 to 12 sheets where the data to be checked is always in col.B.
Can the above code be modified to search and highlight multiple sheets in one pass?

8. Anup Mondal

SIR THIS IS VERY HELP FULL TOPIC YOU COVERED. ITS REALLY HELPS ME LOT. BUT SIR THERE LITTLE EXTENSION ON THIS PROJECT AND IF YOU HELP ME ON THIS I WILL BE VERY GRACE BY YOU BECAUSE IT WILL SAVE MY LOT OF TIME.

MY QUESTION IS CAN THIS CODE WORK TO COMPARE MULTIPLE SHEETS LIKE “SHEET1” WITH “SHEET2” WITH “SHEET3” ?