Compare Two Worksheets
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,
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" wrote:
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.
.
|