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

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