If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A string of data in a cell
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. |
#2
|
|||
|
|||
A string of data in a cell
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 -- Jacob (MVP - Excel) "Elton Law" wrote: 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. |
#3
|
|||
|
|||
A string of data in a cell
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/200...e-elses-macro/ To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm Hope this helps, Hutch "Elton Law" wrote: 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. |
#4
|
|||
|
|||
A string of data in a cell
Hi Jacob and and Tom,
Both work.... It is great job. Thanks so much for help. I am very much appreciated. "Jacob Skaria" wrote: 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 -- Jacob (MVP - Excel) "Elton Law" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|