XLA routines: EE_ArrayCommonElements

Nick's picture
EE_ArrayCommonElements returns the common elements in 2 arrays
Function EE_ArrayCommonElements(array1 As Variant, _
                          array2 As Variant) As Variant
    Dim tempArray     As Variant
    Dim i             As Long
 
'http://excelexperts.com/xla-routines-eeArrayCommonElements    for updates on this function

    ' start with a single element
    ReDim tempArray(0)
 
    ' if element in first array exists in second array, keep it
    For i = LBound(array1) To UBound(array1)
        If EE_IsInArray(array2, CStr(array1(i))) Then  ' found!
        'if filter(array2,CStr(array1(i)))
            ReDim Preserve tempArray(UBound(tempArray) + 1)
            tempArray(UBound(tempArray)) = array1(i)
        End If
    Next i
 
    ' first element is Empty, so shift all elements one position up
    For i = LBound(tempArray) To UBound(tempArray) - 1
        tempArray(i) = tempArray(i + 1)
    Next i
    ' remove last element
    If UBound(tempArray) <> 0 Then
        ReDim Preserve tempArray(LBound(tempArray) To UBound(tempArray) - 1)
    End If
 
    EE_ArrayCommonElements = tempArray
End Function