Removing Duplicates In Excel Column.

[ANONYMOUS123456]

Hi, Everyone!

I have column A, In which every cell has words including spaces in between. Every cell has leading and trailing spaces as well. I have denoted words with english alphabets A, B, C, D, E, F and G. I want to perform an operation on column A such that the duplicate words are removed from whole column A irrespective of the excel cell they are resinding on and the unique words are placed in front of every cell in column A as differentiated by column B. E.g In column A2 there are A,B,C,D which are four words with spaces in between and also with leading and trailing spaces. After removing duplicates, the B2 is A B C D. But in A3, B,C,D and E four words present. but after removing duplicates, only E word remains in B3 because B,C and D are duplicate words which are already taken by B2. Similarly, B4 contains F G because D and E are duplicactes and already taken by B2.

 

[Marcelo Branco]

Maybe this macro...

Sub aTest()
    Dim dict As Object, spl As Variant
    Dim rCell As Range, strAux As String, i As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    For Each rCell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        strAux = ""
        spl = Split(rCell)
        For i = 0 To UBound(spl)
            If spl(i) <> "" Then
                If Not dict.exists(spl(i)) Then
                    dict(spl(i)) = Empty
                    strAux = strAux & spl(i) & " "
                End If
            End If
        Next i
        rCell.Offset(, 1) = " " & strAux
    Next rCell
End Sub

Hope this helps

M.

 

[ANONYMOUS123456]

Thanx for giving me the solution. Working perfect.