If you want to send a sample/abbreviated file to
(E-Mail Removed) 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
"(E-Mail Removed)" 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
>
>
Similar Threads
