Excel VBA - count the duplicates
I have two arrays which contains string values in them. Let us consider the example as below.
- Array 1: Computer science, Artificial Intelligence
- Array 2: Eclipse, MS, RAD, Linux, Artificial Intelligence
I need to find the number of duplicates in both of my arrays. In the above case, I need the total duplicate value as 1 (since Artificial Intelligence is in both Array1 and Array2). Is there a way to do this in VBA?
This function assumes that arr2 is a 1-dimensional array:
Function ArrDupCount(ByVal arr1 As Variant, ByVal arr2 As Variant) As Long Dim varElement As Variant Dim lMatch As Long On Error Resume Next For Each varElement In arr1 lMatch = 0 lMatch = WorksheetFunction.Match(varElement, arr2, 0) If lMatch > 0 Then ArrDupCount = ArrDupCount + 1 Next varElement On Error GoTo 0 End Function
To use it:
Sub tgr() Dim arr1 As Variant Dim arr2 As Variant arr1 = Array("Computer science", "Artificial Intelligence") arr2 = Array("Eclipse", "MS", "RAD", "Linux", "Artificial Intelligence") MsgBox ArrDupCount(arr1, arr2) ' => 1 End Sub