Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows for differences
I 'm in dire need of a solution to this problem, I need to match sheet1 to
sheet 2 by entire rows and show the non matching rows on sheet3. Any help with this would be greatly appreciated. I've been working this for a week and cannot come up with a solution. Sheet 1 Sheet 2 Sheet3 1 2 3 4 1 2 3 4 6 7 8 8 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 9 6 7 8 8 e f g h a b c d a b c d e f h g e f g h e f h g |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows for differences
"Brian Madsen" wrote in message ink.net... I 'm in dire need of a solution to this problem, I need to match sheet1 to sheet 2 by entire rows and show the non matching rows on sheet3. Any help with this would be greatly appreciated. I've been working this for a week and cannot come up with a solution. Sheet 1 Sheet 2 Sheet3 1 2 3 4 1 2 3 4 6 7 8 8 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 9 6 7 8 8 e f g h a b c d a b c d e f h g e f g h e f h g (Whoops, looks like I'm also in need of a formatting tutor) Should look like: Sheet 1 Sheet 2 1 2 3 4 1 2 3 4 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 8 a b c d a b c d e f g h e f h g Sheet 3 6 7 8 8 6 7 8 9 e f g h e f h g |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows for differences
OK. I'll bite.
Basic Strategy: 1. Read the data from each of sheets 1 and 2 into arrays. 2. Compare row by row, and identify mismatches 3. Create an array of the mismatched and write to sheet 3 Basic questions: 1. Do we need to check to see if a row on sheet1 exists on sheet2, or do we need to add a check to ensure that rows on sheet2 exist on sheet1? 2. I assume that row checking means verify exactly the same value in the same column on each sheet? Let me know. Alex J "Brian Madsen" wrote in message ink.net... "Brian Madsen" wrote in message ink.net... I 'm in dire need of a solution to this problem, I need to match sheet1 to sheet 2 by entire rows and show the non matching rows on sheet3. Any help with this would be greatly appreciated. I've been working this for a week and cannot come up with a solution. Sheet 1 Sheet 2 Sheet3 1 2 3 4 1 2 3 4 6 7 8 8 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 9 6 7 8 8 e f g h a b c d a b c d e f h g e f g h e f h g (Whoops, looks like I'm also in need of a formatting tutor) Should look like: Sheet 1 Sheet 2 1 2 3 4 1 2 3 4 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 8 a b c d a b c d e f g h e f h g Sheet 3 6 7 8 8 6 7 8 9 e f g h e f h g |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows for differences
Try This ( I have tested, but I'm not positive it will be logically correct
for every possible case) AlexJ Dim xc1 As New Collection Dim xc2 As New Collection Dim xc3 As New Collection Dim cols As Long Sub test() Dim Array1 As Variant Dim Array2 As Variant Dim Array3 As Variant Dim sht1 As Worksheet Dim sht2 As Worksheet Dim sht3 As Worksheet Dim Xfer() As Variant Set sht1 = ThisWorkbook.Sheets("sheet1") Set sht2 = ThisWorkbook.Sheets("sheet2") Set sht3 = ThisWorkbook.Sheets("sheet3") Array1 = sht1.Range("A1").CurrentRegion Array2 = sht2.Range("A1").CurrentRegion cols = UBound(Array1, 2) 'Assumes all arrays are same width ReDim Xfer(1 To 1, 1 To cols) a1rows = UBound(Array1, 1) a2rows = UBound(Array2, 1) For i = 1 To a1rows For j = 1 To cols Xfer(1, j) = Array1(i, j) Next j xc1.Add Xfer, "1." & CStr(i) Next i For i = 1 To a2rows For j = 1 To cols Xfer(1, j) = Array2(i, j) Next j xc2.Add Xfer, "2." & CStr(i) Next i Call MixMatch ReDim Array3(1 To xc3.Count, 1 To cols) For i = 1 To xc3.Count For j = 1 To cols Array3(i, j) = xc3(i)(1, j) Next j Next i sht3.Range("A1").CurrentRegion.ClearContents Set WriteRange = sht3.Range("A1").Resize(xc3.Count, cols) WriteRange.Value = Array3 End Sub Sub MixMatch() Dim ItemsSame As Boolean Dim RowsSame() As Boolean Dim match() As Boolean ReDim RowsSame(1 To 2, 1 To Application.WorksheetFunction.Max(xc1.Count, xc2.Count)) For i = 1 To xc1.Count For j = 1 To xc2.Count ItemsSame = False For k = 1 To cols If xc1.Item(i)(1, k) = xc2(j)(1, k) Then ItemsSame = True Else ItemsSame = False End If Next k If ItemsSame Then RowsSame(1, i) = ItemsSame RowsSame(2, j) = ItemsSame End If Next j Next i For i = 1 To xc1.Count If RowsSame(1, i) = False Then xc3.Add xc1.Item(i) Next i For i = 1 To xc2.Count If RowsSame(2, i) = False Then xc3.Add xc2.Item(i) Next i End Sub "Brian Madsen" wrote in message ink.net... "Brian Madsen" wrote in message ink.net... I 'm in dire need of a solution to this problem, I need to match sheet1 to sheet 2 by entire rows and show the non matching rows on sheet3. Any help with this would be greatly appreciated. I've been working this for a week and cannot come up with a solution. Sheet 1 Sheet 2 Sheet3 1 2 3 4 1 2 3 4 6 7 8 8 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 9 6 7 8 8 e f g h a b c d a b c d e f h g e f g h e f h g (Whoops, looks like I'm also in need of a formatting tutor) Should look like: Sheet 1 Sheet 2 1 2 3 4 1 2 3 4 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 8 a b c d a b c d e f g h e f h g Sheet 3 6 7 8 8 6 7 8 9 e f g h e f h g |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching rows for differences
Alex, You are the man!!!!!! I'll play with it for a while and check out
all the variables, but so far it works beautifully. You have saved me so much time! If you were local, I'd owe you a beer!!!!!!! IF you were a woman and I were single, I'd marry you. "Alex J" wrote in message ... Try This ( I have tested, but I'm not positive it will be logically correct for every possible case) AlexJ Dim xc1 As New Collection Dim xc2 As New Collection Dim xc3 As New Collection Dim cols As Long Sub test() Dim Array1 As Variant Dim Array2 As Variant Dim Array3 As Variant Dim sht1 As Worksheet Dim sht2 As Worksheet Dim sht3 As Worksheet Dim Xfer() As Variant Set sht1 = ThisWorkbook.Sheets("sheet1") Set sht2 = ThisWorkbook.Sheets("sheet2") Set sht3 = ThisWorkbook.Sheets("sheet3") Array1 = sht1.Range("A1").CurrentRegion Array2 = sht2.Range("A1").CurrentRegion cols = UBound(Array1, 2) 'Assumes all arrays are same width ReDim Xfer(1 To 1, 1 To cols) a1rows = UBound(Array1, 1) a2rows = UBound(Array2, 1) For i = 1 To a1rows For j = 1 To cols Xfer(1, j) = Array1(i, j) Next j xc1.Add Xfer, "1." & CStr(i) Next i For i = 1 To a2rows For j = 1 To cols Xfer(1, j) = Array2(i, j) Next j xc2.Add Xfer, "2." & CStr(i) Next i Call MixMatch ReDim Array3(1 To xc3.Count, 1 To cols) For i = 1 To xc3.Count For j = 1 To cols Array3(i, j) = xc3(i)(1, j) Next j Next i sht3.Range("A1").CurrentRegion.ClearContents Set WriteRange = sht3.Range("A1").Resize(xc3.Count, cols) WriteRange.Value = Array3 End Sub Sub MixMatch() Dim ItemsSame As Boolean Dim RowsSame() As Boolean Dim match() As Boolean ReDim RowsSame(1 To 2, 1 To Application.WorksheetFunction.Max(xc1.Count, xc2.Count)) For i = 1 To xc1.Count For j = 1 To xc2.Count ItemsSame = False For k = 1 To cols If xc1.Item(i)(1, k) = xc2(j)(1, k) Then ItemsSame = True Else ItemsSame = False End If Next k If ItemsSame Then RowsSame(1, i) = ItemsSame RowsSame(2, j) = ItemsSame End If Next j Next i For i = 1 To xc1.Count If RowsSame(1, i) = False Then xc3.Add xc1.Item(i) Next i For i = 1 To xc2.Count If RowsSame(2, i) = False Then xc3.Add xc2.Item(i) Next i End Sub "Brian Madsen" wrote in message ink.net... "Brian Madsen" wrote in message ink.net... I 'm in dire need of a solution to this problem, I need to match sheet1 to sheet 2 by entire rows and show the non matching rows on sheet3. Any help with this would be greatly appreciated. I've been working this for a week and cannot come up with a solution. Sheet 1 Sheet 2 Sheet3 1 2 3 4 1 2 3 4 6 7 8 8 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 9 6 7 8 8 e f g h a b c d a b c d e f h g e f g h e f h g (Whoops, looks like I'm also in need of a formatting tutor) Should look like: Sheet 1 Sheet 2 1 2 3 4 1 2 3 4 2 3 4 5 2 3 4 5 6 7 8 9 6 7 8 8 a b c d a b c d e f g h e f h g Sheet 3 6 7 8 8 6 7 8 9 e f g h e f h g |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching 2 worksheets to find differences | Excel Discussion (Misc queries) | |||
Easiest way to find differences between Rows? | Excel Discussion (Misc queries) | |||
Matching Rows | Excel Discussion (Misc queries) | |||
Find rows matching Max value | Excel Discussion (Misc queries) | |||
delete all matching rows | Excel Discussion (Misc queries) |