Removing Duplicates from a 2D Array in Excel VBA

The following function removes duplicates from a 2D array assuming the duplicate entries exist in first column.

It uses dictionaries.



Function RemoveDuplicatesFrom2DMatrix(arr As Variant) As Variant()
' here its assumed that key or string which is repeating is first column
Dim dict As Object

'Dim dict As Scripting.Dictionary
Dim key As Variant
Dim OutputArr() As Variant

Set dict = CreateObject("Scripting.Dictionary")

For i = LBound(arr, 1) To UBound(arr, 1)
If dict.Exists(arr(i, 0)) Then
'skip adding this row into dictionary
Else
dict.Add arr(i, 0), arr(i, 1)
End If
Next i
ReDim OutputArr(dict.Count, 2)
i = 0
For Each key In dict.Keys
    OutputArr(i, 0) = key
    OutputArr(i, 1) = dict(key)
    i = i + 1
Next key
RemoveDuplicatesFrom2DMatrix = OutputArr
End Function