[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1497

# Find Common Values between 2 Arrays on Excel VBA

I am looking for a vba function which is going to look into two arrays and bring out the common values.

So very simply put
Array1
1
3
4
5
6

Array 2
6
4
10
12
14
15
16
18

So the function should bring 4 and 6. The size of the two arrays maybe different.
0
awesomejohn19
• 2
1 Solution

Commented:
where would your arrays be? ranges?
0

Commented:
You'll need something like

``````for loop1=lbound(array1) to ubound(array1)
for loop2=lbound(array2) to ubound(array2)
if array1(loop1)=array2(loop2) then
array3(newMatch)=array(loop1)
newmatch=newmatch+1
end if
next loop2
next loop1
``````
0

Commented:
Or..

``````Function GETUNIQUE(ByRef Arr1, ByRef Arr2)

If TypeOf Arr1 Is Range Then Arr1 = Arr1.Value2
If TypeOf Arr2 Is Range Then Arr2 = Arr2.Value2

Dim e, x, i As Long

With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In Arr1
If Len(e) Then .Item(e) = Empty
Next
For Each e In Arr2
If .exists(e) Then .Item(e) = 1
Next
x = Array(.keys, .items)
.RemoveAll
For i = 0 To UBound(x(0))
If x(1)(i) = 1 Then .Item(x(0)(i)) = Empty
Next
If .Count Then GETUNIQUE = .keys
End With

End Function
``````

and call the function

``````Sub kTest()

MsgBox Join(GETUNIQUE(Range("a2:a6"), Range("b2:b9")), vbLf)

End Sub
``````

Kris
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.