Weekly Tips
Every week, TOD will try to present a different "tip" or "trick" to make your use of computers and software more interesting and easy. Usually these are taken from the collections of various helpful hints that have been gathered up and made available on the CDs, which are distributed with most of our classes.
Microsoft Excel - Flag Duplicate Values from two different Worksheets
Let us say you have two separate spreadsheets, each with a column that has similar data (such as names or address) with hundreds of rows. But you need to find where the data has been duplicated between these two. Here is a simple little VBA routine to make duplicate values change to a bold text with a red background.
You will need to enter the following within the workbook through the Visual Basic Editor. The easiest way to get to the VBA Editor is by using the shortcut key combination of ALT+F11. Make sure you select "ThisWorkbook" under the Project Explorer window, and then copy/paste this code into the main window:
Public Sub FindDups()
Dim rng1 As Range
Dim rng2 As Range
Dim cell1 As Range
Dim cell2 As Range
Set rng1 = Worksheets("Sheet1").Range("B:B")
Set rng2 = Worksheets("Sheet2").Range("G:G")
For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For
For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For
If cell1.Value = cell2.Value Then
cell1.Font.Bold = True
cell1.Interior.ColorIndex = 3
cell1.Interior.Pattern = xlSolid
cell2.Font.Bold = True
cell2.Interior.ColorIndex = 3
cell2.Interior.Pattern = xlSolid
End If
Next cell2
Next cell1
End Sub
The above compares everything in column B on Sheet1 to everything in column G on Sheet2, disregarding empty cells. You will need to make modifications to the above code to specify your own worksheet names and columns to compare against.
(01/18/2009)
