Remove Duplicate Numbers in One Cell

  • Thread starter Thread starter Lucky
  • Start date Start date
L

Lucky

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
 
Thanks, Joel! I was onto the array part, but Split was the piece I
was missing. This should work great for me.
Lucky
 
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
Click to expand...

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
 
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.
 
Many thanks to everyone. I ended up using Gary"s procedure,
converting it into a function. Now I'll just stick the function in my
module, send it the contents of each cell in the column, and replace
the contents with the results of the function. Works great! I want to
experiment with all 3 solutions because they all contain interesting
concepts that are new to me. I really appreciate the help.
Lucky
 
Back
Top