How
to find duplicates in two different worksheets in MSExcel


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 
