A string of data in a cell

[Elton Law]

Dear expert,

I got a system generated file.
Say cell A1 to A3 are below.

12,12,12,13,12,12,12,14,12,12,14
15,15,15,13,13,13,14,15,16,17,15
19,19,19,20,20,20,20,20,20,20,19

Is it possible to filter all duplicated numbers and come out like this in
cell B1 to B3?
12,13,14
15,13,14,16,17
19,20

Sequence is not a issue. It doesn't matter.
Thanks.

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=GetUniqueNumString(A1)

Function GetUniqueNumString(strData As String) As String
Dim intCount As Integer, arrData As Variant
For Each varItem In Split(strData, ",")
If InStr("," & GetUniqueNumString & ",", "," & varItem & ",") = 0 Then
GetUniqueNumString = GetUniqueNumString & "," & varItem
End If
Next
GetUniqueNumString = Mid(GetUniqueNumString, 2)
End Function

 

[Tom Hutchins]

You could use this user-defined function:

Public Function ListWithoutDupes(MyRng As Range) As String
Dim x As Long, OutStr As String
Dim RA As Variant, ret As Variant
On Error GoTo LWDerr
If MyRng.Cells.Count > 1 Then
ListWithoutDupes = "ERROR"
Exit Function
End If
OutStr = " "
RA = Split(MyRng.Value, ",")
For x = 0 To UBound(RA)
If Len(RA(x)) > 0 Then
ret = InStr(1, OutStr, RA(x))
If ret = 0 Then
OutStr = OutStr & RA(x) & ","
End If
End If
Next x
OutStr = Trim(OutStr)
ListWithoutDupes = Left(OutStr, Len(OutStr) - 1)
Exit Function
LWDerr:
ListWithoutDupes = vbNullString
End Function

Call it like this in B1:
=ListWithoutDupes(A1)
and copy down.

Paste the function code in a general VBA module in your workbook. If you are
new to user-defined functions (macros), this link to Jon Peltier's site may
be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

 

[Elton Law]

Hi Jacob and and Tom,
Both work.... It is great job.
Thanks so much for help. I am very much appreciated.