When you’d like to compare 2 arrays in EXCEL VBA whether they are equal or not, what would you do? Furthermore, you might get different elements that don’t overlap each other. I’d like to describe the code that how to get differences of 2 arrays. <\/p>\n

At first, you would have to check whether they are equal each other or not because the difference of equal arrays is empty set. Next, you would have to check whether an array is proper subset of another array or not. At last, you could get difference between 2 arrays. <\/p>\n

See formula as below, it is shown all elements of Array1 as “A”, all elements of Array2 as “B” and differences elements from Array1 and Array2 as “A And (not B)”. <\/p>\n

$Array1 - Array2 = A\\ AND\\ (not B) = A \\cap \\neg B$ <\/p>\n<\/p>\n

The following code compares elements between two set and get number of equal elements with double loop, that means to get intersection of 2 sets. <\/p>\n

Option Explicit\r\n\r\nFunction COMPARE_ARRAY(ByRef Array1() As String, ByRef Array2() As String) As Boolean\r\n    Dim i   As Long\r\n    Dim j   As Long\r\n    Dim k   As Long\r\n    k = 0\r\n    For i = LBound(Array1) To UBound(Array1)\r\n        For j = LBound(Array2) To UBound(Array2)\r\n            If Array1(i) = Array2(j) Then\r\n                k = k + 1\r\n            End If\r\n        Next j\r\n    Next i\r\n    If i = j And i = k Then\r\n        COMPARE_ARRAY = True\r\n    Else\r\n        COMPARE_ARRAY = False\r\n    End If\r\nEnd Function\r\n\r\nFunction PROPERSUBSET_ARRAY(ByRef Array1() As String, ByRef Array2() As String) As Boolean\r\n    Dim i   As Long\r\n    Dim j   As Long\r\n    Dim k   As Long\r\n    k = 0\r\n    For i = LBound(Array1) To UBound(Array1)\r\n        For j = LBound(Array2) To UBound(Array2)\r\n            If Array1(i) = Array2(j) Then\r\n                k = k + 1\r\n            End If\r\n        Next j\r\n    Next i\r\n    If k = i Then\r\n        PROPERSUBSET_ARRAY = True\r\n    Else\r\n        PROPERSUBSET_ARRAY = False\r\n    End If\r\nEnd Function\r\n<\/pre>\nConfiguration of the 3rd argument “Compare” specifies which array should be excepted, when it was TRUE the function would except the latter from the former, when it was FALSE then it would except the former from the latter, respectively. It’s assumed that the elements of arrays has no empty string. <\/p>\n\r\nFunction EXCEPT_ARRAY(ByRef Array1() As String, ByRef Array2() As String, ByVal Compare As Boolean) As String()\r\n    Dim i               As Long\r\n    Dim j               As Long\r\n    Dim k               As Long\r\n    Dim InternalAr1()   As String\r\n    Dim InternalAr2()   As String\r\n    Dim Ar1Ar()         As String\r\n    Dim Ar2Ar()         As String\r\n    InternalAr1 = Array1\r\n    InternalAr2 = Array2\r\n    For i = LBound(InternalAr1) To UBound(InternalAr1)\r\n        For j = LBound(InternalAr2) To UBound(InternalAr2)\r\n            If InternalAr1(i) = InternalAr2(j) Then\r\n                InternalAr1(i) = \"\"\r\n                InternalAr2(j) = \"\"\r\n                k = k + 1\r\n            End If\r\n        Next j\r\n    Next i\r\n    k = 0\r\n    For i = LBound(InternalAr1) To UBound(InternalAr1)\r\n        If InternalAr1(i) = \"\" Then\r\n        Else\r\n            ReDim Preserve Ar1Ar(k)\r\n            Ar1Ar(k) = InternalAr1(i)\r\n            k = k + 1\r\n        End If\r\n    Next i\r\n    k = 0\r\n    For j = LBound(InternalAr2) To UBound(InternalAr2)\r\n        If InternalAr2(j) = \"\" Then\r\n        Else\r\n            ReDim Preserve Ar2Ar(k)\r\n            Ar2Ar(k) = InternalAr2(j)\r\n            k = k + 1\r\n        End If\r\n    Next j\r\n    If Compare Then\r\n        EXCEPT_ARRAY = Ar1Ar\r\n    Else\r\n        EXCEPT_ARRAY = Ar2Ar\r\n    End If\r\nEnd Function\r\n<\/pre>\nREFERENCE:
\nHow to get first cell which is referred to formula and last cell which refers to formula in Excel worksheet?<\/a><\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"
When you’d like to compare 2 arrays in EXCEL VBA whether they are equal or not, what would you do? Furth … “How to compare and get differences between 2 arrays in EXCEL VBA?” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":6026,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[6],"tags":[315,316,603,317,318,320,319,153],"class_list":["post-3868","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-array","tag-difference","tag-excel","tag-except","tag-intersection","tag-proper-subset","tag-union","tag-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3868","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3868"}],"version-history":[{"count":10,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3868\/revisions"}],"predecessor-version":[{"id":3879,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3868\/revisions\/3879"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/media\/6026"}],"wp:attachment":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3868"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3868"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3868"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}