Check for values in array

[tiredofit]

If I want to check it a single value exists in an array, this would work:

'taken from https://wellsr.com/vba/2016/excel/check-if-value-is-in-array-vba/

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Function to check if a value is in an array of values
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

However, if I have two arrays, say array1 contains 1000 records in a single column and likewise for array2, is there a quicker way than looping to check if each value in array1 is in array2?

Thanks

 

[Fluff]

If they are single columns, I'd read one of them into a dictionary & loop through the other & check if it's in the dictionary.

 

[tiredofit]

If they are single columns, I'd read one of them into a dictionary & loop through the other & check if it's in the dictionary.

Thanks, that'll do nicely.

 

[Fluff]

You're welcome & thanks for the feedback.