|
Compare Two Worksheets
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. |
Compare Two Worksheets
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. |
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. . |
Compare Two Worksheets
|
| All times are GMT +1. The time now is 05:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com