Using Loops - Is there a better way to finding matching records?

Hi, I want to match records in one sheet to another to identify which records don't exist in the second sheet (as opposed to already existing there) so I can add them to that sheet. I've written the following code which is basically two loops looking for a match on the first field and then the second field. Is there a better approach to this than using loops? I have previously use the FIND functionality which I understand to be faster than loops but couldn't work out how to use it with 2 criteria. I've also appended a file with the code in.Looping to find matching values.xlsm Thanks for any ideas, regards, Neil

Public Sub TransferDataToMemDataLog()

Dim FinalRow As Single  'number variables available throughout this module
Dim i As Integer
Dim j As Long
Dim oSht As Worksheet
Dim oSht1 As Worksheet  'object variable for "MemDataLog"
Dim lastRow As Long
Dim strSearch As String     'Lookup value of members number for use on membership sheet
Dim str2Search As String     'Lookup value of members number for use on membership sheet
Dim t As Integer

    Set oSht = Sheets("Data")  'make sheet active in memory
    Set oSht1 = Sheets("MemDataLog")  'make sheet active in memory

    FinalRow = oSht.Cells(Rows.Count, 1).End(xlUp).Row   'Count last row on "Data" sheet
    lastRow = oSht1.Cells(Rows.Count, 1).End(xlUp).Row   'Count last row on "MemDataLog" sheet

    Set oSht = Sheets("Data")                         'set object variable to "Data"
    Set oSht1 = Sheets("MemDataLog")                  'set object variable to "MemDataLog"
    
'------------------------------------------------------------------------------------------------
'Loop through "Data" sheet 1 record at a time. Take Name value from "Data" sheet and loop through Name
'values on "MemDataLog" to find match. When find match then check if date also matches. If so,
'it is a duplicate and doesn't need to be added to the "MemDataLog" sheet. If no match then add it.

    For i = 2 To FinalRow   'Loop through each record on "Data"
    
        t = 0                               'Set variable values
        strSearch = oSht.Cells(i, 1).Value
        str2Search = oSht.Cells(i, 4).Value
            
        For j = 2 To lastRow    'Loop through each record on "MemDataLog"
            
            If oSht1.Cells(j, 4).Value = str2Search Then 'Check for match on Date field
                If oSht1.Cells(j, 1).Value = strSearch Then    'Check for match on Name field
                    t = 1
                    Exit For
                End If
            End If
        Next j
                
        If t = 0 Then   'If t remains 0 then no match has been found and record needs to be added
        MsgBox str2Search & "on " & strSearch & " " & "has no match, add to MemDataLog sheet"
        End If
    Next i

End Sub

What are the matching column/s ?
A, A&B, A,B&C?

I'm trying to match the first column (Date) and the fourth column (Mem Name)....

Try the attached

Hi AB33, thanks for the input - the updating should be done from Data to "MemDataLog" - I reversed the references to the sheets and it works. I'll give it a thorough check...

I don't quite understand how it's working - I can see you are using arrays but I've no idea what " Set dic = CreateObject("scripting.dictionary") dic.comparemode = 1" is doing. Can you give me some explanation or point me somewhere I can read about the elements as I use the forum to fix things and for education! Presumably it's much faster than my loops solution?

Many thanks for the solution and your time, regards, Neil

MemDataLog has larger data, so the missing could be added to the small sheet which is sheet data.
The code uses dictionary object with arrays.
Dictionary are used to test for the existence of unique key. It is difficult to explain it in one page, but I would suggest, you should be comfortable with arrays before you jump in to dictionary.
dic.comparemode = 1 means the keys you are testing are not case sensitive, i.e. Key (A) and Key (a) are the same, but if change it the 1 in to 0, it becomes case sensitive.
Arrays are much faster than loops. You are basically writing(loading) the range value in to array and you then loop through each array to access the elements within the array. Finally you write it back(copy) in to range.

Read codes written by Jindon.

Thanks, a bit or reading ahead of me I can see! Once more thankyou for your time and help

Neil,
Do not be downhearted!
I learned from scratch(Zero knowledge about codding) in this site