Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing two data worksheets and desciphering differences
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross Referencing two data worksheets and desciphering differences
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 "EricZ" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross referencing | Excel Worksheet Functions | |||
Cross-Referencing Across Worksheets | Excel Worksheet Functions | |||
Cross referencing | Excel Worksheet Functions | |||
Cross Referencing Data and Pasting | Excel Discussion (Misc queries) | |||
Cross referencing data across different workbooks | Excel Discussion (Misc queries) |