VBA looping through two arrays to find entries that DON'T match?

[Sunjinsak]

Hi,

I’ve got a problem here that is driving me INSANE!!!!!!!!!

I have 2 lists of ID numbers; one on a ‘master’ sheet and one on a ‘report’ sheet. I need to list all IDs on the ‘master’ sheet that DO NOT have a corresponding entry on the ‘report’ sheet.

So far I’ve read the relevant lists from each sheet into arrays and am attempting to loop through to find any non matching entries. I have no problem finding entries that DO match (see the example code below – it works perfectly)… but I have no idea how to go about finding entries that DON’T match??

As soon as I change...

If masterArr(x, 1) = reportArr(y, 1) Then…

...to...

If Not masterArr(x, 1) = reportArr(y, 1) Then…

...in the below code I get very unexpected results – it finds the first entry that DOES match and then repeatedly returns it in an infinite loop. If I insert an Else clause strange things also happen.

I just can’t seem to visualise what’s happening inside the loops in my mind so I don’t really know how to go about doing this. I’m sure to minds greater than mine it’s a very simple thing to achieve and I'm missing something painfully obvious. Can anyone help?

Sub View_Outstanding_Reports(ByVal SheetIndex As Integer)
 
Dim masterArr As Variant    'An array to hold the IDs of all staff on the master list
Dim reportArr As Variant    'An array to hold the IDs of all submitted reports
 
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(SheetIndex)
 
Dim LRowReport As Long, LRowMaster As Long  'Variables to hold the number of the last row containing a value
 
LRowReport = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
LRowMaster = Sheets("MasterList").Range("A" & Sheets("MasterList").Rows.Count).End(xlUp).Row
 
masterArr = Sheets("MasterList ").Range("A2:A" & LRowMaster).Value
reportArr = ws.Range("B6:B" & LRowReport).Value
 
Dim col As Collection       'A collection to hold non-matching IDs
Set col = New Collection
 
 
Dim x As Integer, y As Integer
 
    For x = 1 To UBound(masterArr, 1)
   
        For y = 1 To UBound(reportArr, 1)
       
            If masterArr(x, 1) = reportArr(y, 1) Then
                MsgBox "Match found: " & masterArr(x, 1)
            End If
            
        Next y
       
    Next x
 
'Clean up
Set ws = Nothing
 
End Sub


Sub Test()
 
Call View_Outstanding_Reports(ActiveSheet.Index)
 
End Sub

Many thanks!

 

[RedBeard]

No Excel on this machine, but you have a space

[COLOR=#333333]masterArr = Sheets("MasterList ").Range("A2:A" & LRowMaster).Value[/COLOR]

here which doesn't appear on the
line above.

 

[Sunjinsak]

Thanks for pointing that out RedBeard. That's just a typo when I was pasting the code in I think. I can confirm it's correct in my actual code. Too late to edit my OP now though unfortunately.

 

[pbornemeier]

Option Explicit
Sub Test_ReturnItemsNotInA()

    Dim masterArr As Variant
    Dim reportArr As Variant
    Dim absentArr As Variant
    Dim lCount As Long
    
    Dim sWorksheet As String
    
    'Start: Commented out this section for my test
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets(SheetIndex)
    
    Dim LRowReport As Long, LRowMaster As Long  'Variables to hold the number of the last row containing a value
    
    With Sheets("MasterList")
    LRowReport = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    LRowMaster = .Range("A" & .Rows.Count).End(xlUp).Row
    
    masterArr = .Range("A2:A" & LRowMaster).Value
    reportArr = ws.Range("B6:B" & LRowReport).Value
    'End:   Commented out this section for my test
    
    
    'masterArr = Range("A1:A5").Value 'For MTest
    'reportArr = Range("B1:B5").Value 'For Test

    absentArr = ReturnItemsNotInA(masterArr, reportArr)
    
    sWorksheet = "Missing"
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sWorksheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sWorksheet 'After last

    lCount = UBound(absentArr) - LBound(absentArr) + 1
    
    With Worksheets("Missing")
        .Range("A1").Resize(lCount, 1).Value = Application.Transpose(absentArr)
    End With

End Sub

Function ReturnItemsNotInA(aryA As Variant, aryB As Variant) As Variant
    'Receive 2 arrays, compare & return list of items in B not in A
    'Modify code depending on how arrays were generated
    'If 2D with dimensions (1 to Count, 1 to 1) use (lIndex, 1)
    'If 1D with dimension  (1 to Count)         use (lIndex)
    
    Dim oSD As Object
    Dim lIndex As Long
    Dim varK  As Variant
    
    Set oSD = CreateObject("Scripting.Dictionary")
    
    With oSD
        'Inventory Array B
        For lIndex = LBound(aryB) To UBound(aryB)
             .Item(aryB(lIndex, 1)) = .Item(aryB(lIndex, 1)) + 1
        Next
        'Remove items from inventory that are also in Array A
        For lIndex = LBound(aryA) To UBound(aryA)
            If .exists(aryA(lIndex, 1)) Then .Remove (aryA(lIndex, 1))
        Next
        'Get Scripting.Dictionary data to array
        If .Count > 0 Then
            varK = .Keys
        End If
    End With
    
    ReturnItemsNotInA = varK
    
    Set oSD = Nothing

End Function

 

[Craggs82]

so if finding matches works, flag a Boolean, then only write down if the flag is false?

Dim x As Integer, y As Integer, [COLOR="#40E0D0"]Fnd as boolean[/COLOR]
 
    For x = 1 To UBound(masterArr, 1)
        
            [COLOR="#40E0D0"]Fnd = false[/COLOR]
        
           For y = 1 To UBound(reportArr, 1)
       
                 If masterArr(x, 1) = reportArr(y, 1) Then Fnd = true
 
           Next y
       
          [COLOR="#40E0D0"]if Fnd = false then col.add(masterArr(x, 1))[/COLOR]

    Next x

QUOTE]

 

[Norie]

If you have 2 lists of IDs you only need to loop through one, the 'master', and use Application.Match to see if an ID is in/not in the other list.

 

[Sunjinsak]

so if finding matches works, flag a Boolean, then only write down if the flag is false?

Dim x As Integer, y As Integer, [COLOR=#40E0D0]Fnd as boolean[/COLOR]
 
    For x = 1 To UBound(masterArr, 1)
        
            [COLOR=#40E0D0]Fnd = false[/COLOR]
        
           For y = 1 To UBound(reportArr, 1)
       
                 If masterArr(x, 1) = reportArr(y, 1) Then Fnd = true
 
           Next y
       
          [COLOR=#40E0D0]if Fnd = false then col.add(masterArr(x, 1))[/COLOR]

    Next x

Craggs82 that seems to work perfectly! Thank you!!

pbornemeier I get the general gist of what your code is doing but I can't quite follow it. What are the advantages to using your method over Craggs82's suggestion? Certainly having a function that can be re-used in the future is a bonus but as I can't quite follow it I'm not sure if it's worth it or not? Thanks.

Edit: Sorry Norie you posted your solution as I was typing my initial reply. That's certainly an easier solution - wish I'd have thought of that to begin with! I've gone with Craggs82's suggestion for now though as I've already written the code anyway. Thanks.

 

[gallen]

Without writing all your code for you, this code takes 2 ranges and checks the first one against the 2nd and reports back any that were not matched.

By following it line by line you should see the logic. (Most of the code below is comments so not too difficult to understand)

Sub FindNoDuplicates()
'This sub takes to columns of data (A1:A10 & B1:B10) and compares every element in column 1 and checks _
if there is a match in column 2. If not it reports back in the form of a message box

    Dim i As Integer, x As Integer
    Dim sResults As String
    
    'TODO: Set these ranges to the ID Ranges on sheets
    Arr1 = Range("A1:A10")
    Arr2 = Range("B1:B10")
    
    'Loop through all in first array
    For i = 1 To UBound(Arr1)
        'Loop through all in 2nd array
        For x = 1 To UBound(Arr2)
            'if the current element of the first array matches the current element of the 2nd array, we have a match
            If Arr1(i, 1) = Arr2(x, 1) Then
                'Match was found
                Exit For
            End If
        Next x
        'as we exit the loop early, if x id gretaer than the number of elements then no match was found
        If x > UBound(Arr2) Then
            'No match was found
            sResults = sResults & Arr1(i, 1) & vbNewLine
        End If
    Next i
    
    MsgBox sResults, vbInformation, "IDs Not Found"
End Sub

Edit: Just saw you have your answer. I'll get my coat

 

[Sunjinsak]

Edit: Just saw you have your answer. I'll get my coat

I'll get your coat for you. It's the least I can do

In all seriousness though thanks for the reply - it all helps and I learn from each one.

 

[pbornemeier]

If the master list has 60 items and the report list has 30 items Craggs82's code will perform 60 x 30 =1800 actions.
The Scripting dictionary code passes through the master list once and the report list once for 60+30 = 90 actions
If that code executes in a satisfactory amount of time stick with it.

Norie's suggestion is probably the best.

Scripting Dictionary is very powerful, my example showed a limited use. This thread Scripting Dictionary and several links in talk about when /why to use a scripting dictionary.