logo

Finding records that don't match (Microsoft Excel)
J D posted at Saturday, June 06, 2009 10:10 AM

I have two separate Excel worksheets containing three fields which are common (but each contains other fields).  There is no field which uniquely identifies a record in either worksheet - even the combination of those three fields wouldn't uniquely identify a specific record.  I need to match records in worksheet A to worksheet B and identify those records in A which do not have a match in B (on the three common fields).

I tried solving this with VBA and also through an SQL statement (after importing the worksheets into tables in Access).  I'm stumped on what's the best way to accomplish this.  Any help would be extrememly appreciated!

Thanks in advance!
Joe

Reply    Reply Using Power Editor
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0
VBA to compare sheets
Robert Pattinson provided a rated reply to J D on Saturday, June 06, 2009 4:40 PM

Hi Joe,

This code will evaluate 2 fields and find matches or fields that don't match depending on your needs.  Next, add the unmatched record to a new sheet or bold the record in code to identify records.  Download zip file containing the code at www.jettechnical.com/sampletutorials/checkrecords.zip

Function FindUnmatchedRecords()
Dim strCompare As String
Dim strCompare1 As String
    'set up the sheet you will be comparing to on the
    'first cell to be checked
    Range("A2").Select
    'get the first sheet
    Sheets("Sheet1").Select
    'alter this to the column you want to compare.
    'this assumes that row 1 will be the header
    Range("A2").Select
    'do until the field is empty
    Do Until ActiveCell.Value = ""
        strCompare = ActiveCell.Value
        Sheets("Sheet2").Select
        strCompare2 = ActiveCell.Value
        If strCompare2 <> strCompare Then
            Sheets("Sheet3").Select
            ActiveCell.Value = strCompare2
            'set up for the next record
            ActiveCell.Offset(1, 0).Select
            'go back to Sheet2
            Sheets("Sheet2").Select
        End If
        'move to the next cell
        ActiveCell.Offset(1, 0).Select
        Sheets("Sheet1").Select
        'move to the next cell
        ActiveCell.Offset(1, 0).Select
    Loop
      

End Function

To better understand how this works, open the project, open the macro (ALT + F11) , minimize the macro window so that you can see both the spreadsheet and the macro window.  Place the cursore in the function and press F8 to step through the code and what how it executes.  Then you'll be able to modify as you need.

Happy Coding!

Robert Pattinson

 

 

 

Reply    Reply Using Power Editor
Robert Pattinson is a Senior Software Engineer at JET Technical, Inc
  Rank Winnings Points
February 0 $0.00 0
January 0 $0.00 0


Didn't Find The Answer You Were Looking For?

EggHeadCafe has experts online right now that may know the answer to your question.  We pay them a bonus for answering as many questions as they can.  So, why not help them and yourself by becoming a member (free) and ask them your question right now?
Ask Question In Live Forum

If you have an OpenID and do not want to become a member of the EggHeadCafe forum, you can also sign on to Chat Chaos and post your question to our real time Silverlight chat application.
Ask Question In Chat Chaos










  $1000 Contest    [)ia6l0 iii - $228  |  Jonathan VH - $161  |  Huggy Bear - $135  |  F Cali - $95  |  egg egg - $94  |  more Advertise  |  Privacy  |   (c) 2010