I looking for a fast macro dat will compare two excel sheets(containing large amount of data) row wise..here i want to take one row from first excel sheet and compare with all rows in another excel sheet.if any of the row from first sheet completely matches with any of the rows in second excel sheet then its ok ,but if doesnt match then copy dat row to 3rd sheet...
for example
Sheet 1
column1 column2
aa bb
dd dd
Sheet 2
column1 column2
e e
aa bb
Sheet 3
column1 column2
dd dd
I tried wid the following code:
Sub Compare()
'
' Macro1 Macro
'
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
End Sub
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim dupRow As Boolean
Dim r As Long, c As Integer, m As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim dupCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
lr3 = 1
For i = 1 To lr1
dupRow = True
Application.StatusBar = "Comparing worksheets " & Format(i / maxR, "0 %") & "..."
For r = 1 To lr2
For c = 1 To maxC
ws1.Select
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(i, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
dupRow = False
Exit For
Else
dupRow = True
End If
Next c
If dupRow Then
Exit For
End If
Next r
If Not dupRow Then
dupCount = dupCount + 1
ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, maxC)).Select
Selection.Copy
Worksheets("Sheet3").Select
Worksheets("Sheet3").Range(Worksheets("Sheet3").Cells(lr3, 1), Worksheets("Sheet3").Cells(lr3, maxC)).Select
Selection.PasteSpecial
lr3 = lr3 + 1
ws1.Select
For t = 1 To maxC
ws1.Cells(i, t).Interior.ColorIndex = 19
ws1.Cells(i, t).Select
Selection.Font.Bold = True
Next t
End If
Next i
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
m = dupCount
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " Rows contain different values!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub
its working as required but got stucked for long data.
Then got an idea to use varient for faster comparison.
Now clueless how to go ahead...can anyone help me in this ?
Moderator's Edit: Use code tags when posting code. To do so, select your code and click on the # icon above. I have done it for you this time.
Bookmarks