Cross Referencing two data worksheets and desciphering differences

[EricZ]

Need immediate help!
I have two excel worksheets ; both consisting of simuliar data. Trying to
dicepher differences between the two worksheets , using sheet 2 as the master
sheet in which all data is to be referenced againist. Cannot seem derive the
answers I need. (* see example below). How would you approach the result
shown below.
Sheet 1
ComA Colm B Colm C Colm D Colm E Colum F
Area Item # Desc Length Width Height
001 333 Cookies 10 12 15
001 444 bread 12 12 12
001 555 cake 4 4 4
Sheet2
ComA Colm B Colm C Colm D Colm E Colum F
Area Item # Desc Length Width Height
002 333 Cookies 10 16 1
002 444 bread 12 2 17
002 555 cake 4 4 4

Results needed :
Sheet 3 - Match =
001 555 cake 4 4 4
002 555 cake 4 4 4

Sheet 4 -No Match
ComA Colm B Colm C Colm D Colm E Colum F
Area Item # Desc Length Width Height
001 333 Cookies 10 12 15
001 444 bread 12 12 12

 

[Mike]

Test on a copy of your workbook
Sub compareSheets()
Dim sh1looper As Long
Dim sh1Lastrow As Long
Dim sh1cellPointer As Variant
Dim sh2looper As Long
Dim sh2Lastrow As Long
Dim sh2cellPointer As Variant

sh1Lastrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
sh2Lastrow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

i = 2
r = 2
For sh2looper = i To sh2Lastrow
Set sh2cellPointer = Worksheets("Sheet2").Cells(sh2looper, 1).Offset(0,
1)
For sh1looper = r To sh1Lastrow
Set sh1cellPointer = Worksheets("Sheet1").Cells(sh1looper,
1).Offset(0, 1)
If sh2cellPointer = sh1cellPointer Then
Exit For ' found what we need get out
End If
r = r + 1
Next
If sh2cellPointer = sh1cellPointer _
And sh2cellPointer.Offset(0, 1) = sh1cellPointer.Offset(0, 1) _
And sh2cellPointer.Offset(0, 2) = sh1cellPointer.Offset(0, 2) _
And sh2cellPointer.Offset(0, 3) = sh1cellPointer.Offset(0, 3) Then

sh3Lastrow = Worksheets("Sheet3").Range("A" &
Rows.Count).End(xlUp).Row
Worksheets("Sheet3").Range("A" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, -1)
Worksheets("Sheet3").Range("B" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer
Worksheets("Sheet3").Range("C" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 1)
Worksheets("Sheet3").Range("D" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 2)
Worksheets("Sheet3").Range("E" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 3)
Worksheets("Sheet3").Range("F" & sh3Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 4)
Worksheets("Sheet3").Range("G" & sh3Lastrow).Offset(1, 0).Value
= "MATCH"

Worksheets("Sheet3").Range("A" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer.Offset(0, -1)
Worksheets("Sheet3").Range("B" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer
Worksheets("Sheet3").Range("C" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer.Offset(0, 1)
Worksheets("Sheet3").Range("D" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer.Offset(0, 2)
Worksheets("Sheet3").Range("E" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer.Offset(0, 3)
Worksheets("Sheet3").Range("F" & sh3Lastrow).Offset(2, 0).Value
= sh2cellPointer.Offset(0, 4)
Worksheets("Sheet3").Range("G" & sh3Lastrow).Offset(2, 0).Value
= "MATCH"
Else
sh4Lastrow = Worksheets("Sheet4").Range("A" &
Rows.Count).End(xlUp).Row
Worksheets("Sheet4").Range("A" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, -1)
Worksheets("Sheet4").Range("B" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer
Worksheets("Sheet4").Range("C" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 1)
Worksheets("Sheet4").Range("D" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 2)
Worksheets("Sheet4").Range("E" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 3)
Worksheets("Sheet4").Range("F" & sh4Lastrow).Offset(1, 0).Value
= sh1cellPointer.Offset(0, 4)
Worksheets("Sheet4").Range("G" & sh4Lastrow).Offset(1, 0).Value
= "NO-MATCH"
End If
r = 2
i = i + 1
Next
End Sub