D
dids72
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
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