Drop duplicate values from string

**sc0tt** · 06-13-2008, 10:03 PM

I'm using this formulat to print out a string of numbers;

here's the current formula: =SUBSTITUTE(TRIM(A1&" "&B1&" "&C1)," ","~")

But I need to modify it so that it dumps duplicate values like; 4~4~6 ----> 4~6

so if A1 and/or B2 and/or C1 are equal to one another; only one of the two or more duplicate values is displayed.

Any thoughts?

**Ron Coderre** · 06-13-2008, 11:08 PM

With
A2:C2 containing numbers or blanks...

Try this:

D2: =SUBSTITUTE(TRIM(A2&" "&IF(B2=A2,"",B2)&" "&IF(OR(C2=A2,C2=B2),"",C2))," ","~")

Does that help?

**sc0tt** · 06-14-2008, 08:15 PM

Very helpful; Can you show me how this formula would work for 6 sets of numbers?

=SUBSTITUTE(TRIM(A8&" "&IF(B8=A8,"",B8)&" "&IF(OR(C8=A8,C8=B8),"",C8))," ","~")

this works with 3,but now it turns out I actually need six.

But I need to modify it so that it dumps duplicate values out of 6 source numbers; 4~4~6~5~5~4 ----> 4~6~5

so if A1 and/or B2 and/or C1 and/or D1 and/or E1 and/or F1 are equal to one another; only one of the two or more duplicate values is displayed.

stretching my brain;
G2: =SUBSTITUTE(TRIM(A2&" "&IF(B2=A2,C2=A2,D2=A2,E2=A2,"",B2)&" "&IF(OR(F2=A2,F2=B2,F2=C2,F2=D2,F2=E2),"",F2))," ","~")

**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