Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Rows on different Worksheets and Output Difference's to other Worksheets.
Hello,
I am trying to figure out how to compare the data from rows across multiple columns on different worksheets. What I am trying to do is output the data that is in Worksheet 1 and not in Worksheet 2 into Worksheet 3. Then in addition also output the data that appears in Worksheet 2 but not in Worksheet 1 into Worksheet 4. One key aspect would be if the same data appears multiple times' for example it may appear 2 times on Worksheet 1 but only 1 time in Worksheet 2. The second appearance of the data would be copied to Worksheet 3. For Example: Worksheet 1 Worksheet 2 Column A Column B Column A Column B 080107 AN3205 080107 AN3205 080207 AN3205 080107 AN3500 080107 AN3500 080207 AN3501 080107 AN3501 080207 AN3501 080107 AN3510 080107 AN3510 080107 AN3510 When the Compare macro is run it would produce the following output. Note that data entry 080107 AN3510 appears on Worksheet 3 because it appears 2 time's on Worksheet 1 but only 1 time on Worksheet 2. Also, note that data entry 080207 AN3501 appears 2 times on Worksheet 4 because it appears 2 times on Worksheet 2 and not at all on Worksheet 1. Worksheet 3 Worksheet 4 Column A Column B Column A Column B 080207 AN3205 080207 AN3501 080107 AN3510 080207 AN3501 I am trying to modify the code below, that Tom Ogilvy posted in the past, to address my needs. I have added questions to the code to see if someone could help me understand it. If someone would help me to understand or given me a little direction on how to proceed. It would be greatly appreciated. Sub ProcessData() Dim rng1 As Range, rng2 As Range Dim cell As Range, rw As Long Dim cnt As Long, c As Range Dim firstAddress As String Dim i As Long, bFound As Boolean 'Does "Range(.Cells(2, 7)" indicate Columns 2 and 7 or 2 through 7? 'Is "Cells(Rows.Count, 7).End(xlUp))" counting the number of rows in Column 7? With Worksheets("Sheet1") Set rng1 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With With Worksheets("Sheet2") Set rng2 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With rw = rng2.Rows(rng2.Rows.Count).Row + 1 For Each cell In rng1 Set c = rng2.Find(cell, LookIn:=xlValues) 'What is this part of the code doing? If Not c Is Nothing Then firstAddress = c.Address bFound = False Do cnt = 0 For i = -2 To -6 Step -1 If cell.Offset(0, i) < c.Offset(0, i) Then Exit For End If cnt = cnt + 1 Next i If cnt = 5 Then bFound = True Exit Do End If Set c = rng2.FindNext(c) Loop While c.Address < firstAddress If bFound = False Then cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Else cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Next cell End Sub Again any help you would be able to provide would be greatly appreciated. I don't know to much about VBA and am trying to learn to get this done for a friend of mine. Thanks, Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Rows on different Worksheets and Output Difference's to ot
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Rows on different Worksheets and Output Difference's to ot
On Sep 19, 9:22 am, Tom Ogilvy
wrote: If you want to send a sample/abbreviated file to with your source worksheets and a worksheet showing expected output annotated as to why each item was placed there (in the output sheet - that covers all contingencies), then I can provide you a macro that performs the action. The wordwrap in the posting makes it difficult to decipher -- regards, Tom Ogilvy " wrote: Hello, I am trying to figure out how to compare the data from rows across multiple columns on different worksheets. What I am trying to do is output the data that is in Worksheet 1 and not in Worksheet 2 into Worksheet 3. Then in addition also output the data that appears in Worksheet 2 but not in Worksheet 1 into Worksheet 4. One key aspect would be if the same data appears multiple times' for example it may appear 2 times on Worksheet 1 but only 1 time in Worksheet 2. The second appearance of the data would be copied to Worksheet 3. For Example: Worksheet 1 Worksheet 2 Column A Column B Column A Column B 080107 AN3205 080107 AN3205 080207 AN3205 080107 AN3500 080107 AN3500 080207 AN3501 080107 AN3501 080207 AN3501 080107 AN3510 080107 AN3510 080107 AN3510 When the Compare macro is run it would produce the following output. Note that data entry 080107 AN3510 appears on Worksheet 3 because it appears 2 time's on Worksheet 1 but only 1 time on Worksheet 2. Also, note that data entry 080207 AN3501 appears 2 times on Worksheet 4 because it appears 2 times on Worksheet 2 and not at all on Worksheet 1. Worksheet 3 Worksheet 4 Column A Column B Column A Column B 080207 AN3205 080207 AN3501 080107 AN3510 080207 AN3501 I am trying to modify the code below, that Tom Ogilvy posted in the past, to address my needs. I have added questions to the code to see if someone could help me understand it. If someone would help me to understand or given me a little direction on how to proceed. It would be greatly appreciated. Sub ProcessData() Dim rng1 As Range, rng2 As Range Dim cell As Range, rw As Long Dim cnt As Long, c As Range Dim firstAddress As String Dim i As Long, bFound As Boolean 'Does "Range(.Cells(2, 7)" indicate Columns 2 and 7 or 2 through 7? 'Is "Cells(Rows.Count, 7).End(xlUp))" counting the number of rows in Column 7? With Worksheets("Sheet1") Set rng1 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With With Worksheets("Sheet2") Set rng2 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With rw = rng2.Rows(rng2.Rows.Count).Row + 1 For Each cell In rng1 Set c = rng2.Find(cell, LookIn:=xlValues) 'What is this part of the code doing? If Not c Is Nothing Then firstAddress = c.Address bFound = False Do cnt = 0 For i = -2 To -6 Step -1 If cell.Offset(0, i) < c.Offset(0, i) Then Exit For End If cnt = cnt + 1 Next i If cnt = 5 Then bFound = True Exit Do End If Set c = rng2.FindNext(c) Loop While c.Address < firstAddress If bFound = False Then cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Else cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Next cell End Sub Again any help you would be able to provide would be greatly appreciated. I don't know to much about VBA and am trying to learn to get this done for a friend of mine. Thanks, Rich- Hide quoted text - - Show quoted text - Hi Tom, I just sent you a sample file. Thank you very much for taking the time to help me. Thanks, Rich |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Rows on different Worksheets and Output Difference's t
Sent you are response.
-- Regards, Tom Ogilvy " wrote: On Sep 19, 9:22 am, Tom Ogilvy wrote: If you want to send a sample/abbreviated file to with your source worksheets and a worksheet showing expected output annotated as to why each item was placed there (in the output sheet - that covers all contingencies), then I can provide you a macro that performs the action. The wordwrap in the posting makes it difficult to decipher -- regards, Tom Ogilvy " wrote: Hello, I am trying to figure out how to compare the data from rows across multiple columns on different worksheets. What I am trying to do is output the data that is in Worksheet 1 and not in Worksheet 2 into Worksheet 3. Then in addition also output the data that appears in Worksheet 2 but not in Worksheet 1 into Worksheet 4. One key aspect would be if the same data appears multiple times' for example it may appear 2 times on Worksheet 1 but only 1 time in Worksheet 2. The second appearance of the data would be copied to Worksheet 3. For Example: Worksheet 1 Worksheet 2 Column A Column B Column A Column B 080107 AN3205 080107 AN3205 080207 AN3205 080107 AN3500 080107 AN3500 080207 AN3501 080107 AN3501 080207 AN3501 080107 AN3510 080107 AN3510 080107 AN3510 When the Compare macro is run it would produce the following output. Note that data entry 080107 AN3510 appears on Worksheet 3 because it appears 2 time's on Worksheet 1 but only 1 time on Worksheet 2. Also, note that data entry 080207 AN3501 appears 2 times on Worksheet 4 because it appears 2 times on Worksheet 2 and not at all on Worksheet 1. Worksheet 3 Worksheet 4 Column A Column B Column A Column B 080207 AN3205 080207 AN3501 080107 AN3510 080207 AN3501 I am trying to modify the code below, that Tom Ogilvy posted in the past, to address my needs. I have added questions to the code to see if someone could help me understand it. If someone would help me to understand or given me a little direction on how to proceed. It would be greatly appreciated. Sub ProcessData() Dim rng1 As Range, rng2 As Range Dim cell As Range, rw As Long Dim cnt As Long, c As Range Dim firstAddress As String Dim i As Long, bFound As Boolean 'Does "Range(.Cells(2, 7)" indicate Columns 2 and 7 or 2 through 7? 'Is "Cells(Rows.Count, 7).End(xlUp))" counting the number of rows in Column 7? With Worksheets("Sheet1") Set rng1 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With With Worksheets("Sheet2") Set rng2 = .Range(.Cells(2, 7), .Cells(Rows.Count, 7).End(xlUp)) End With rw = rng2.Rows(rng2.Rows.Count).Row + 1 For Each cell In rng1 Set c = rng2.Find(cell, LookIn:=xlValues) 'What is this part of the code doing? If Not c Is Nothing Then firstAddress = c.Address bFound = False Do cnt = 0 For i = -2 To -6 Step -1 If cell.Offset(0, i) < c.Offset(0, i) Then Exit For End If cnt = cnt + 1 Next i If cnt = 5 Then bFound = True Exit Do End If Set c = rng2.FindNext(c) Loop While c.Address < firstAddress If bFound = False Then cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Else cell.EntireRow.Copy Worksheets("sheet2").Cells(rw, 1) rw = rw + 1 End If Next cell End Sub Again any help you would be able to provide would be greatly appreciated. I don't know to much about VBA and am trying to learn to get this done for a friend of mine. Thanks, Rich- Hide quoted text - - Show quoted text - Hi Tom, I just sent you a sample file. Thank you very much for taking the time to help me. Thanks, Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! -How to compare two worksheets; multiple rows of data=result | Excel Worksheet Functions | |||
Compare 1st 8 digits of rows in three worksheets | Excel Worksheet Functions | |||
Compare multiple column rows on different worksheets. | Excel Programming | |||
compare two worksheets and delete rows | Excel Programming | |||
vba - output Selections from worksheets | Excel Programming |