Check for values in array

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,621
Office Version
  1. 2019
Platform
  1. Windows
If I want to check it a single value exists in an array, this would work:

Code:
'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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,212
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,621
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,212
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,014
Messages
5,834,929
Members
430,326
Latest member
tomwax46

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top