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

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

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
Asked:
awesomejohn19
  • 2
1 Solution
 
nutschCommented:
where would your arrays be? ranges?
0
 
nutschCommented:
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

Open in new window

0
 
krishnakrkcCommented:
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

Open in new window


and call the function

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

Open in new window


Kris
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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