I threw this together and tested it.
it works on your sample data.
I then tested it on a sample file of 135000 records.
it took about 6 minutes to do 135,000 records.
I tested a variation that puts a "flag" into column J,
then, once complete, filters on this column and highlights the rows.
the time dropped to 34 seconds.
Highlighting takes a LOT of CPU time.
'--------------------------------------------------
Option Explicit
Public Dict_Data1, Dict_Data2
Sub Compare()
Dim Sht1, Sht2
Dim nRows, r, sData, rArray, inx
Dim tstart, tstop, TElapsed, TMin, TSec, msg
'---------------------------------------------------------
Application.ScreenUpdating = False
'---------------------------------------------------------
tstart = Timer
Set Dict_Data1 = CreateObject("Scripting.Dictionary")
Set Dict_Data2 = CreateObject("Scripting.Dictionary")
Dict_Data1.RemoveAll
Dict_Data2.RemoveAll
'---------------------------------------------------------
'Identify sheets to compare
'---------------------------------------------------------
Sht1 = "Data1"
Sht2 = "Data2"
'---------------------------------------------------------
' Clear Highlighting
'---------------------------------------------------------
Sheets(Sht1).Select
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Sheets(Sht2).Select
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'---------------------------------------------------------
' Load data from Sht1 into Dictionary Object
'---------------------------------------------------------
nRows = Application.WorksheetFunction.CountA(Sheets(Sht1).Range("A1:A" &
Sheets(Sht1).Rows.Count))
For r = 2 To nRows
If (r Mod 1000 = 0) Then Application.StatusBar = "Loading " & Sht1 & "
row " & r & " of " & nRows
sData = Trim(Sheets(Sht1).Cells(r, "A").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "B").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "C").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "D").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "F").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "H").Value)
sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "I").Value)
If (Not Dict_Data1.exists(sData)) Then
Dict_Data1.Add sData, r
'--------------------------------------------------------------
Else ' In case of duplicate rows, collect all row numbers
'--------------------------------------------------------------
Dict_Data1.Item(sData) = Dict_Data1.Item(sData) & "," & r
End If
Next
'---------------------------------------------------------
' Process Sht2 data, compare to sht1
'---------------------------------------------------------
nRows = Application.WorksheetFunction.CountA(Sheets(Sht2).Range("A1:A" &
Sheets(Sht2).Rows.Count))
For r = 2 To nRows
If (r Mod 1000 = 0) Then Application.StatusBar = "Processing " & Sht2 &
" row " & r & " of " & nRows
sData = Trim(Sheets(Sht2).Cells(r, "A").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "B").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "C").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "D").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "F").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "H").Value)
sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "I").Value)
If (Dict_Data1.exists(sData)) Then
'-----------------------------------
'highlight row in sht2
'-----------------------------------
Sheets(Sht2).Select
With Sheets(Sht2).Range(Cells(r, "A"), Cells(r, "I")).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'-----------------------------------
'highlight rows in sht1
'-----------------------------------
Sheets(Sht1).Select
rArray = Split(Dict_Data1.Item(sData), ",")
For inx = 0 To UBound(rArray)
With Sheets(Sht1).Range(Cells(rArray(inx), "A"),
Cells(rArray(inx), "I")).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Next inx
End If
Next
Application.StatusBar = False
Application.ScreenUpdating = True
'----------------------------------------------------
msg = "Finished: "
tstop = Timer
TElapsed = tstop - tstart
TMin = 0
TMin = TElapsed \ 60
TSec = TElapsed Mod 60
msg = msg & Chr(13) & Chr(13)
If (TMin > 0) Then msg = msg & TMin & " mins "
msg = msg & TSec & " sec"
'----------------------------------------------------
MsgBox msg
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
>________________________________
> From: Missy786 <misbah.ali...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Friday, April 25, 2014 6:10 AM
>Subject: $$Excel-Macros$$ find duplicate rows of data between sheets macro
>
>
>
>I have a large amount of data in two worksheet. I would like the macro, to
>search the two sheets and look for duplicate rows of data by comparing columns
>A, B, C, D, F, H, I between the two worksheets. Show the results by
>highlighting the duplicate rows in both sheets.
>
>I have attached a sample input file:
>https://app.box.com/s/ob5vir4qhg59tnm7tz8l
>
>I have also attached desired output file:
>https://app.box.com/s/xd6o2ofjycgnhzz8qv4f
>
>
>Please help. Many thanks for your help and support.
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
