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?

Answers


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

Need Your Help

C# adding empty literal in empty tablecell results in

c# asp.net literals tablecell

I'm reading a menu control code. The code uses a tablerow in a table to construct the menu. After each menu item tablecell, it adds the code:

When rotating my viewController, the status bar doesn't rotate with the with. How to fix it?

objective-c ios6 uiinterfaceorientation

Everyone. I've got an iOS6 ipad app to update. The problem I met is that only one view controller needs autorotate, and I don't find out the correct method to do this.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.