Help! -How to compare two worksheets; multiple rows of data=result
 

I am trying to compare two worksheets with one having relatable data (see
illustration below-Item). The relatable field has row information which is
pertinent to the 'comparable' field.
Sheet # 2 is the 'master-data' ; using gathered referneced data entered in
sheet # 1 , I am trying to derrive 'matching results and 'unmatching results'
Tried using 'pivot table ; did not receivie likeable results. Is their a
'macros' I could use????
(Please see illustration below)

Sheet # 1 -Entered data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12
001 222 Fruit 12 14 22
001 333 bread 2 12 24

Sheet #2 - Static Data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
002 333 bread 1 10 5
002 222 Fruit 10 12 20
002 111 Egg 10 11 12
002 444 Drink 4 4 4

Would like results of.......
Sheet # 3 - Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12

Sheet # 4 - Not Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 222 Fruit 12 14 22
001 333 bread 2 12 24
002 444 Drink 4 4 4

Help! -How to compare two worksheets; multiple rows of data=result
 

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



"EricZ" wrote:

I am trying to compare two worksheets with one having relatable data (see
illustration below-Item). The relatable field has row information which is
pertinent to the 'comparable' field.
Sheet # 2 is the 'master-data' ; using gathered referneced data entered in
sheet # 1 , I am trying to derrive 'matching results and 'unmatching results'
Tried using 'pivot table ; did not receivie likeable results. Is their a
'macros' I could use????
(Please see illustration below)

Sheet # 1 -Entered data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12
001 222 Fruit 12 14 22
001 333 bread 2 12 24

Sheet #2 - Static Data
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
002 333 bread 1 10 5
002 222 Fruit 10 12 20
002 111 Egg 10 11 12
002 444 Drink 4 4 4

Would like results of.......
Sheet # 3 - Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 111 Egg 10 11 12

Sheet # 4 - Not Matching
Colm 1 Colm2 Colm3 Colm 4 Colm 5 Colm 6
Area Item Desc Length Width Height
001 222 Fruit 12 14 22
001 333 bread 2 12 24
002 444 Drink 4 4 4