CODEWITHSUNDEEP
arraysvbaduplicates
J. Chapman
J. Chapman

Reputation: 315

Fastest way to detect duplicate values in array? (VBA)

To detect duplicate values in a VBA array, I am currently using a function which checks whether, for each value in an array, the subsequent values in the array are different:

Public Function ContainsDuplicateKeys() As Boolean

    Dim inputKeyArray As Variant
    inputKeyArray = MyWorksheet.Range("MyTable[InputKey]")

    Dim i As Long
    Dim j As Long

    For i = LBound(inputKeyArray) To UBound(inputKeyArray)
        For j = i + 1 To UBound(inputKeyArray)
            If inputKeyArray(i, 1) = inputKeyArray(j, 1) Then
                ContainsDuplicateKeys = True
                Exit Function
            End If
        Next
    Next

    ContainsDuplicateKeys = False

End Function

To check whether an array of n values are unique, this method involves doing 1 + 2 + ... + (n-1) comparisons. So checking the uniqueness of 10,000 inputs requires up to 49,995,000 comparisons, which takes about 13 seconds.

On the contrary, the time taken for Excels 'Remove Duplicates' functionality to complete for the same number of inputs is near instantaneous. This implies that there must be a far more efficient way of achieving what I am attempting to do. Can anyone suggest what this might be?

Upvotes: 2

Views: 15101

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could use Application.Match() function:

Public Function ContainsDuplicateKeys() As Boolean
    Dim inputKeyArray As Variant
    inputKeyArray = MyWorksheet.Range("MyTable[InputKey]")

    Dim i As Long
    For i = UBound(inputKeyArray) To LBound(inputKeyArray) Step -1
        If Application.Match(inputKeyArray(i, 1), inputKeyArray, 0) <> i Then
            ContainsDuplicateKeys = True
            Exit Function
        End If
    Next
End Function

Upvotes: 1

Related Questions