A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

A string of data in a cell



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 02:19 PM posted to microsoft.public.excel.worksheet.functions
Elton Law[_2_]
external usenet poster
 
Posts: 94
Default 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  
Old June 2nd, 2010, 05:21 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old June 2nd, 2010, 05:23 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default 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  
Old June 3rd, 2010, 10:00 AM posted to microsoft.public.excel.worksheet.functions
Elton Law[_2_]
external usenet poster
 
Posts: 94
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.