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 stackoverflow.com/questions/3017852/… — Marcucciboy2
– Marcucciboy2, Commented Oct 8, 2018 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
– JNevill, Commented Oct 8, 2018 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
– JNevill, Commented Oct 8, 2018 at 16:17
"Thirding" that link. Also this one covers everything about dictionaries. Dictionaries are the easiest way to handle collection/storage of unique data. — ashleedawg
– ashleedawg, Commented Oct 8, 2018 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
– JNevill, Commented Oct 8, 2018 at 16:18

DisplayName · Accepted Answer · 2018-10-08 16:49:02Z

1

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

edited Oct 8, 2018 at 16:49

answered Oct 8, 2018 at 16:38

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mike01010 Over a year ago

so in an array(not range),u still have to loop though?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related