Prev: Setting workbooks
Next: Insert a square in a cell with VBA
From: Lucky on 16 Jan 2010 06:42
My worksheet has one column in which each cell has one and two-digit
numbers separated by spaces, such as

1 18 22 5 1 31 8 11 6 1

I want to remove all the duplicate numbers in each cell, leaving only
one instance of each number in there. (1 and 11 are different
numbers, of course). I've tried a few solutions, but they're clunky
and inelegant and take a long time to do these thousands of cells.

Anyone have a better way?
Thanks,
Lucky
From: joel on 16 Jan 2010 07:20

This isn't eligant but it is pretty straight forward. Use split to put
the numbers into an array. Perform a Buble Test on the array by
comparing each element with all the other element. If a duplicate is
found then put replace the number with an X. Then remove all the
numbers from the array.

Chip Pearson has some solutions for counting the duplicates and non
duplicate in the string. He also has methods for getting duplices when
each number is in a seperate cells. You could use TexttoColumn method
and then use one of Chip's solutions. If you where going to use
TextTocolumns I would then use Advance filter to get unique values.


Sub test()

'Date in cells with spaces are treat as string
'Use the line below for testing
MyArray = "1 18 22 5 1 31 8 11 6 1"

NumberArray = Split(MyArray, " ") 'split line around spaces

For i = LBound(NumberArray) To UBound(NumberArray)
'remove any spaces in the number
NumberArray(i) = Val(Trim(NumberArray(i)))
Next i

'replace all duplicates with empty string
For i = LBound(NumberArray) To (UBound(NumberArray) - 1)
For j = (i + 1) To UBound(NumberArray)
If NumberArray(i) = NumberArray(j) Then
NumberArray(j) = "X"
End If

Next j
Next i

MyArray = ""
For i = LBound(NumberArray) To UBound(NumberArray)
If NumberArray(i) <> "X" Then
If MyArray = "" Then
MyArray = NumberArray(i)
Else
MyArray = MyArray & " " & NumberArray(i)
End If
End If
Next i

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170687

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: Lucky on 16 Jan 2010 07:36
Thanks, Joel! I was onto the array part, but Split was the piece I
was missing. This should work great for me.
Lucky
From: Ron Rosenfeld on 16 Jan 2010 07:40
On Sat, 16 Jan 2010 03:42:16 -0800 (PST), Lucky <kingofwhiles(a)aol.com> wrote:

>My worksheet has one column in which each cell has one and two-digit
>numbers separated by spaces, such as
>
>1 18 22 5 1 31 8 11 6 1
>
>I want to remove all the duplicate numbers in each cell, leaving only
>one instance of each number in there. (1 and 11 are different
>numbers, of course). I've tried a few solutions, but they're clunky
>and inelegant and take a long time to do these thousands of cells.
>
>Anyone have a better way?
>Thanks,
>Lucky

Here's a short Macro that will do that.

As written, it will return the value in the adjacent column, for
troubleshooting. But if it is what you want, you can change that easily in the
code.

===========================
Option Explicit
Sub RemoveDups()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\b\d+\b)(?=.*?\1)"
For Each c In Selection
'when debugged, change c.Offset(..).value to
'c.value
c.Offset(0, 1).Value = _
WorksheetFunction.Trim(re.Replace(c.Text, " "))
Next c
End Sub
============================
--ron
From: Gary''s Student on 16 Jan 2010 07:51
Select a cell and:

Sub uniqueification()
Dim coll As Collection
Dim s As String
Set coll = New Collection

ary = Split(Selection.Value, " ")
ub = UBound(ary)
lb = LBound(ary)
If ub = 0 Then Exit Sub

On Error Resume Next
For i = lb To ub
coll.Add ary(i), CStr(ary(i))
Next

For i = 1 To coll.Count
If i = 1 Then
s = coll(1)
Else
s = s & " " & coll(i)
End If
Next
Selection.Value = s
End Sub

You can, of course, use a loop for more cells.
--
Gary''s Student - gsnu200909


"Lucky" wrote:

> My worksheet has one column in which each cell has one and two-digit
> numbers separated by spaces, such as
>
> 1 18 22 5 1 31 8 11 6 1
>
> I want to remove all the duplicate numbers in each cell, leaving only
> one instance of each number in there. (1 and 11 are different
> numbers, of course). I've tried a few solutions, but they're clunky
> and inelegant and take a long time to do these thousands of cells.
>
> Anyone have a better way?
> Thanks,
> Lucky
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Setting workbooks
Next: Insert a square in a cell with VBA