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

Question

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?

This couldn't hurt as a reference https://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array — Marcucciboy2, Oct 08 '18 at 16:14
Seconding that link by @Marcucciboy2 Specifically the suggestion to use a Dictionary instead of an array which will insure no duplicates. — JNevill, Oct 08 '18 at 16:15
Also... why not use "Remove Duplicate" functionality before loading your array (copy that range to a temp sheet, RemoveDuplicates()), load your array, delete temp range. — JNevill, Oct 08 '18 at 16:17
"Thirding" that link. Also **[this one](https://excelmacromastery.com/vba-dictionary/)** covers *everything* about dictionaries. Dictionaries are the easiest way to handle collection/storage of **unique** data. — ashleedawg, Oct 08 '18 at 16:17
Lastly... if your potential duplicates were in a single dimension array, then you could first sort, then dedup which would be a TON faster (if you are hard pressed to use arrays here). — JNevill, Oct 08 '18 at 16:18
Possible duplicate of [VBA, remove duplicates from array](https://stackoverflow.com/questions/11870095/vba-remove-duplicates-from-array) — ashleedawg, Oct 08 '18 at 16:21
And if you're using a Mac there's a drop-in Dictionary implementation here: https://sysmod.wordpress.com/2011/11/02/dictionary-class-in-vba-instead-of-scripting-dictionary/ — Tim Williams, Oct 08 '18 at 16:27

DisplayName · Answer 1 · 2018-10-08T16:49:02.767

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

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

1 Answers1