Sometime back we provided the solution for comparing two excel worksheets and highlight the duplicate entries. Our readers were constantly looking for a macro to compare two columns in the same worksheet and highlight the duplicates. Here is a step by step procedure to compare data in two columns of same worksheet in excel and highlight the duplicate entries. This Macro also updates the operation’s progress in status bar and helps user in estimating the time taken for comparison.
First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.
Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “Compare” or a name of your choice and click on Create button.
Clicking on Create button will launch the Visual Basic editor as below.
Copy below code and paste it in the editor.
' Compare Macro
' compare two different columns in the active worksheet
Dim c1 As Integer, c2 As Integer
Dim i As Long, k As Long, count As Long
Dim lr1 As Long, lr2 As Long
Dim cf1 As String, cf2 As String
Dim WS As Worksheet
' Set the worksheet name
Set WS = Worksheets("Sheet1")
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Application.DisplayAlerts = True
' Setting columns 1 & 2 for comparison (A and B in excel)
c1 = 1
c2 = 2
lr1 = .Rows.count
For i = 2 To lr1
For k = 2 To lr1
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = WS.Cells(i, c1).FormulaLocal
cf2 = WS.Cells(k, c2).FormulaLocal
On Error GoTo 0
If cf1 = cf2 Then
count = count + 1
WS.Cells(i, c1).Interior.ColorIndex = 19
Selection.Font.Bold = True
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox count & " cells contain same values!", vbInformation, _
"Compare column " & c1 & " with " & c2
Save this Macro by closing the editor.
Select active sheet (Sheet1 in this example) and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Duplicate entries will be highlighted in first column.
In the above Macro, it is assumed that first row is a header row. Also, worksheet names is set as “Sheet1″ (refer variable WS in above macro) and columns to be compared are considered to be “1″ and “2″ (refer variables c1 and c2). To change the worksheet names/column numbers or customize this Macro further, follow below steps.
- Go to Tools->Macro->Macros. Select the Macro “Compare” and click on “Edit” button.
- This will launch the Visual Basic Editor.
- Customize the code as per the requirements.
- Save the Macro.
Hope you find this article useful, your suggestions and feedback are always welcome. Thank You.