Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Compare excel sheet data

Posted on 2011-05-08
5
Medium Priority
?
335 Views
Last Modified: 2012-05-11
Hi,

I need to write a macro to compare 2 sheets of an excel workbook and write the differences to a 3rd sheet. For example, go through every row in column A of sheet 1 and check that the value exists anywhere in column A of sheet 2, then check all values in col B of sheet 1 to see if they exist anywhere within col B of sheet 2, etc...
if any value doesnt exist i would like that value written to the corresponding column in sheet 3

I've attached some code that compares col A with col B in the same sheet and writes any errors to col C

Cheers
macro2.txt
0
Comment
Question by:DiCanio13
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:wchh
ID: 35717418
Try macro below...
Sub CopyExceptionSheet()

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
Dim Cnt As Long

With Sheets("Sheet1")
    Set r1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

With Sheets("Sheet2")
    Set r2 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

Sheets("Sheet3").Range("1:" & Rows.Count).EntireRow.Delete
LastRow = 0
For Each r3 In r1
    Dim rng1 As Range
    Set rng1 = r2.Find(What:=r3.Value, After:=r2.Cells(1), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)
    If rng1 Is Nothing Then
       LastRow = LastRow + 1
       Sheets("Sheet3").Range("A" & LastRow).Value = r3.Value
    End If
Next r3

For Each r3 In r2
    Dim rng2 As Range
    Set rng2 = r1.Find(What:=r3.Value, After:=r1.Cells(1), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)
    If rng2 Is Nothing Then
       LastRow = LastRow + 1
       Sheets("Sheet3").Range("A" & LastRow).Value = r3.Value
    End If
Next r3

End Sub

Open in new window

0
 
LVL 8

Accepted Solution

by:
wchh earned 2000 total points
ID: 35717425
Or copy entire row...
Sub CopyExceptionSheet()

Dim r1 As Range, r2 As Range, r3 As Range

With Sheets("Sheet1")
    Set r1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

With Sheets("Sheet2")
    Set r2 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

Sheets("Sheet3").Range("1:" & Rows.Count).EntireRow.Delete
LastRow = 0
For Each r3 In r1
    Dim rng1 As Range
    Set rng1 = r2.Find(What:=r3.Value, After:=r2.Cells(1), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)
    If rng1 Is Nothing Then
       LastRow = LastRow + 1
       r3.EntireRow.Copy Sheets("Sheet3").Range("A" & LastRow)
       'Sheets("Sheet3").Range("A" & LastRow).Value = r3.Value
    End If
Next r3

For Each r3 In r2
    Dim rng2 As Range
    Set rng2 = r1.Find(What:=r3.Value, After:=r1.Cells(1), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False)
    If rng2 Is Nothing Then
       LastRow = LastRow + 1
       r3.EntireRow.Copy Sheets("Sheet3").Range("A" & LastRow)
       'Sheets("Sheet3").Range("A" & LastRow).Value = r3.Value
    End If
Next r3

End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35717494
You might find this article very useful - just written by brettdj:  http:/A_4003.html

Dave
0
 

Author Comment

by:DiCanio13
ID: 35717601
Hi wchh,

When i try to run your macros above, nothing seems to happen...
0
 

Author Closing Comment

by:DiCanio13
ID: 35717633
thanks wchh, works now!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question