Drop duplicate values from string

**Ron Coderre** · 06-14-2008, 08:44 PM

With
A2:F2 containing numbers or blanks
Try this formula...

G2: =SUBSTITUTE(TRIM(A2&" "&IF(B2=A2,"",B2)&" "&
IF(COUNTIF(A2:B2,C2),"",C2)&" "&IF(COUNTIF(A2:C2,D2),"",D2)&" "&
IF(COUNTIF(A2:D2,E2),"",E2)&" "&IF(COUNTIF(A2:E2,F2),"",F2))," ","~")

If it get messier than that, I'd recommend creating
a User Defined Function (UDF) with VBA.

I hope that helps.

**jindon** · 06-14-2008, 09:10 PM

UDF, if you like...
1) hit Alt + F11
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again to get back to Excel
Use in cell like
=sc0tt(A1:Z10,"~")

Function sc0tt(rng As Range, JoinString As String) As String
Dim r As Range
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each r In rng
        If (r.Value <> "") * (Not .exists(r.Text))) Then _
            sc0tt = sc0tt & IIf(sc0tt <> "", JoinString, "") & r.Text
    Next
End With
End Function