CODEWITHSUNDEEP
vbaexcel
thetax
thetax

Reputation: 131

Excel VBA: Function that removes duplicates in a single cell

I need the most efficient way to create an Excel function in VBA that removes the duplicates in a cell:

The input cell (A1) should contain a text like that:

"First_element, Second_element, Third_element, Second_element, Fourth_element"

I need a formula such as:

= REMOVEDUPLICATES(A1)

That produces the following output in B2:

"First_element, Second_element, Third_element, Fourth_element"

It is important that every element is followed by a comma-and-space ", " except the final element.

Upvotes: 0

Views: 492

Answers (1)

Storax
Storax

Reputation: 12207

Try this function

Function RemoveDuplicates(inp As String)
Dim dict As Object
Const DELIMITER = ","
    Set dict = CreateObject("Scripting.Dictionary")

    Dim vdat As Variant
    vdat = Split(inp, DELIMITER)

    Dim i As Long
    For i = LBound(vdat) To UBound(vdat)

        If dict.Exists(vdat(i)) Then
        Else
            dict.Add vdat(i), vdat(i)
        End If
    Next i

    vdat = dict.Keys
    RemoveDuplicates = Join(vdat, DELIMITER)

End Function

Upvotes: 5

Related Questions