Remove duplicates from single cell

Petros, here is a modification of something similar I had to do. It uses a dictionary object to hold the unique keys which are then concatenated back into the result. Once you create it, you can use it like any other function. I don't really have a lot of time right now, so there is no error handling.

Function UniqueValsInCell(ByVal Target As Range)

    Dim i As Integer
    Dim Dictionary As Object
    Dim arKeys() As Variant
    Dim arVals() As String
    Dim strReturn As String
        
    Set Dictionary = CreateObject("Scripting.dictionary")
    
    arVals() = Split(Target.Value, ",")
    strReturn = ""
    For i = 0 To UBound(arVals()) Step 1
        If Not Dictionary.exists(Trim(arVals(i))) Then
            Dictionary.Add Trim(arVals(i)), 1
        End If
    Next i
    
    arKeys = Dictionary.keys
    
    For i = LBound(arKeys()) To UBound(arKeys()) Step 1
        If strReturn = "" Then
            strReturn = arKeys(i)
        Else
            strReturn = strReturn & ", " & arKeys(i)
        End If
    Next i

    UniqueValsInCell = strReturn
    
End Function

Greg

Originally Posted by AB33

Hi Greg,
The dic will handle differently if the words are case sensitive.
You might need to add
option compare to your dic.
Second thought- as it is a function, the case might not matter.

Ah, I had not thought of case sensitivity. Thank you for pointing that out.

Thank you gsnidow. BTW far too advanced for me. A lot of new things to understand

Petros, not sure if you mean the code itself is too advanced, or if you do not know what to do with it. I'll assume the latter. Open your workbook, and press the 'alt' and 'f11' keys at the same time. This will open up the Visual Basic Editor. You may or may not see the project explorer on the left of the screen, and if you don't, click View>Project Explorer. Once the project explorer is visible, you should see something that says 'VBAProject (YourWorkbookName)'. Right click it, and choose insert>module. A module is just a container for code. When you do this, the right hand window should open to the module you just inserted. Copy and paste the code I provided in the module. Once the code is there, you can use the function in a worksheet cell. For example, if your list of names is in cell 'A1', then in cell 'B1' you can enter '=UniqueValsInCell(A1)', and it will return the answer. If none of this makes sense, there are countless examples on the net about getting started with VBA in Excel.

Greg