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 (about 12MB) to see the macro in action: