CODEWITHSUNDEEP
excel-formula
Kismat Shrestha
Kismat Shrestha

Reputation: 11

Removing duplicates in a cell in excel

I have multiple cells in excel which have duplicates in them and A column has numerous such cells. For e.g. A1=(30JNK11BR004 30JNK11AA004 30JNK11AA005 30JNK11BR004 30JNK11AA005). Between each text there is a linefeed. How do I remove the duplicates?

Upvotes: 1

Views: 92

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

If one has the Dynamic Array formula UNIQUE:

=TEXTJOIN(CHAR(10),TRUE,UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(A1,CHAR(10),"</b><b>")&"</b></a>","//b")))

enter image description here

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

With cells like:

enter image description here

Select the cells you wish to process and run this VBA macro:

Sub deDupl()
    Dim cell As Range, chr10 As String, arr
    Dim c As Collection, a, temp As String
    Dim i As Long
    chr10 = Chr(10)
    For Each cell In Selection
        arr = Split(decap(cell.Value), chr10)
        Set c = New Collection
        On Error Resume Next
            For Each a In arr
                c.Add a, CStr(a)
            Next a
        On Error GoTo 0
        temp = ""
        For i = 1 To c.Count
            temp = IIf(temp = "", c.Item(i), temp & chr10 & c.Item(i))
        Next i
        cell.Value = encap(temp)
    Next cell
End Sub

Public Function decap(s As String) As String
    decap = Mid(s, 2, Len(s) - 2)
End Function

Public Function encap(s As String) As String
    encap = "(" & s & ")"
End Function

Cell afterwards:

enter image description here

Upvotes: 0

Related Questions