How to find duplicates in two different worksheets in MS-Excel

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

Home


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:
                                                        

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010