Compare Two Worksheets

Jerry · Feb 26, 2005

I have looked pretty hard at trying to find an answer to
my problem. I thought it would be an easy task to find
but now I have been strung out for days trying to get
this resolved. The issue is I want to compare one row of
data in one work sheet to all the rows in the second
worksheet. Say I have data from A thru H on one worksheet
and the same range of data on the other "master
worksheet". I want to compare row one to all the rows on
the master worksheet and if there is no match to move
that row to worksheet 3; then continue to row two and
compare that row to all the rows in the master sheet and
so on. The rows on the master worksheet and the
comparison worksheet all have the same layout of data:

Master Sheet Comparison Sheet No Match Sheet

Whse Sku Sale Code Whse Sku Sale Code Whse Sku Sale Code
P 1234 ED C 8910 LL M 1234 ED
D 4567 PU M 1234 ED D 4567 YB
C 8910 LL W 1112 LY
W 1112 LY D 4567 YB

Each cell in the whole row must match the master sheet
rows exactly otherwise the comparison row moves to the No
Match Sheet3. I hope I have given enough information. I
would really appreciate any help you could offer. Thanks
in advance.

Jerry J.

Guest · Feb 26, 2005

Hi Jerry,
I have something strted her, but cam up with some questions, because I don't
have your data. This is the what Ihave so far, questions afterward:
Sub Compare()
Worksheets("Comparison").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Whse = ActiveCell.Value
SKU = ActiveCell.Offset(0, 1).Value
SaleCode = ActiveCell.Offset(0, 2).Value
Whse2 = ActiveCell.Offset(0, 3).Value
SKU2 = ActiveCell.Offset(0, 4).Value
SaleCode2 = ActiveCell.Offset(0, 5).Value
Whse3 = ActiveCell.Offset(0, 6).Value
SKU3 = ActiveCell.Offset(0, 7).Value
SaleCode3 = ActiveCell.Offset(0, 8).Value
Worksheets("Master").Select
On Error GoTo ErrorHandler

Range("A:A").Select
Selection.Find(What:=(Whse), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
FirstFindRow = ActiveCell.Row
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Find(What:=(SKU), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 2 Then GoTo ErrorHandler
Selection.Find(What:=(SaleCode), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 3 Then GoTo ErrorHandler
Selection.Find(What:=(Whse2), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 4 Then GoTo ErrorHandler
Selection.Find(What:=(SKU2), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 5 Then GoTo ErrorHandler
Selection.Find(What:=(SaleCode2), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 6 Then GoTo ErrorHandler
Selection.Find(What:=(Whse3), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 7 Then GoTo ErrorHandler
Selection.Find(What:=(SKU3), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 8 Then GoTo ErrorHandler
Selection.Find(What:=(SaleCode3), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Column <> 9 Then GoTo ErrorHandler
Stop ' Match do nothing
Loop
ErrorHandler:
If Err.Number = 91 Then Stop 'NO MATCH move to sheet NoMatch
'or look below
End Sub

Question: In the sample data you provided the 1rst warehouse does not repeat
itself in the first column, but I am guesssing that it will? If it does not
repeat itself, then this becomes much easier. In the code under ErrorHandler:
there is not a match, but only the 1rst occurance of warehouse1 has been
checked and it may be possilbe to move the data to the "NoMatch" worksheet.
If other occurances of Warehouse1 exist in column "A", they have not yet been
checked to see if they will match. Much more complicated for me anyway.

Jerry · Feb 26, 2005

David

Thanks for your reply. I have a sample spreadsheet I
could send you. Your assumption is right, the warehouse
will eventually repeat itself as will the sku and sale
code. I did not realize this would be so complicated to
do. Let me know if you need the spreadsheet I have. Thank
you.
Jerry

-----Original Message-----
Hi Jerry,
I have something strted her, but cam up with some questions, because I don't
have your data. This is the what Ihave so far, questions afterward:
Sub Compare()
Worksheets("Comparison").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Whse = ActiveCell.Value
SKU = ActiveCell.Offset(0, 1).Value
SaleCode = ActiveCell.Offset(0, 2).Value
Whse2 = ActiveCell.Offset(0, 3).Value
SKU2 = ActiveCell.Offset(0, 4).Value
SaleCode2 = ActiveCell.Offset(0, 5).Value
Whse3 = ActiveCell.Offset(0, 6).Value
SKU3 = ActiveCell.Offset(0, 7).Value
SaleCode3 = ActiveCell.Offset(0, 8).Value
Worksheets("Master").Select
On Error GoTo ErrorHandler

Range("A:A").Select
Selection.Find(What:=(Whse), After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,