Eliminating Duplicates

[Parker]

I have 5 cells going across my spreadsheet, that contain
c c c j q respectively. I want to note, in another cell,
that J Q and C have occured, but I don't want the C to
show up 3 times. How do I do this?

 

[RB Smissaert]

One way of doing this.
Can't remember where I got the idea from to use a collection to remove
duplicates.
I don't think it is the fastest way, but it looks nice.
To test put the data in cells A1 to F1.

Function RemoveDuplicates(ByRef arr1 As Variant) As Variant

'removes duplicates from an Excel row, passed as an array
'--------------------------------------------------------

Dim NoDupes As Collection
Dim i As Long
Dim UB As Long
Dim arr2()

Set NoDupes = New Collection

UB = UBound(arr1, 2)

'The next statement ignores the error caused
'by attempting to add a duplicate key to the collection.
'The duplicate is not added - which is just what we want!
'--------------------------------------------------------
On Error Resume Next
For i = 1 To UB
NoDupes.Add arr1(1, i), CStr(arr1(1, i))
'Note: the 2nd argument (key) for
'the Add method must be a string
'--------------------------------
Next
On Error GoTo 0

ReDim arr2(1, 1 To NoDupes.Count)

'copy the collection to the new array
'------------------------------------
For i = 1 To NoDupes.Count
arr2(1, i) = NoDupes(i)
Next

RemoveDuplicates = arr2

End Function


Sub test()

Dim i As Long
Dim strUniques As String
Dim arr()

arr = Range(Cells(1), Cells(6))

arr = RemoveDuplicates(arr)

For i = 1 To UBound(arr, 2)
strUniques = strUniques & arr(1, i)
Next

Cells(8).Value = strUniques

End Sub


RBS

 

[Tom Ogilvy]

Most of the code originated at John Walkenbach's site.

 

[RB Smissaert]

Looked this up and I think the merits here must go to J.G. Hussey.

RBS

 

[Tom Ogilvy]

John is pretty straightforward about his sources. Based on what he says,
the code appears to be an original implementation of an idea of J.G Hussey.

For instance
http://www.devx.com/tips/Tip/12038

Since most of your code is a direct lift from John's site, I think the
merits must go to John.

Or maybe this guy thought it all up:

http://www.win-consult.com/Tips/LibrarySource.asp?App=Excel&Lib=ListComboNoDuplicates
he copyrighted it in 2001
Seems like you used much more than the idea to use a collection since your
code is pretty much identical to John's
Not sure why you want to begrudge John credit for his work.

--
Regards,
Tom Ogilvy

 

[RB Smissaert]

Tom,

I think unless the code is truly amazing and fantastic it is a bit of a
waste of time to get into an argument where
it originated from. Most programmers must have lots of code that they can't
remember where it originated from.
They might know where they got it from but that is not the same as who wrote
it in the first place.
I can see now that the code in question for a large part came from John
Walkenbach and credit to him.
I think though that the essential bit came from J.G. Hussey as John said
himself to credit to him as well.
When I post a bit of code I never claim to have written it in the first
place, all I do is help somebody with a problem.
I think it would be a waste of time if everytime somebody posted a bit of
code he had to trace where it originated from
and mention that in the post. I agree though that in general it is better to
refer to somebody's website or less so to another newgroup posting if the
problem was solved before.

RBS

 

[Tom Ogilvy]

This code (and derivatives) is probably the most frequently posted/cited
code I see in the newsgroup. You said you couldn't recall where you got it,
so I just cited the source as an assist. I was surprised you even had a
response (other than perhaps thank you for the reference/reminder), but your
response was even more surprising in that you seemed to want to deny John
his due.

Apparently the code is amazing and fantastic in that it has probably spread
J.G. Hussey's idea well beyond the limited audience which may have noted it
in VB programmer's journal. But that is neither here nor there. As I
said, I was just attempting to assist your failed recollection for the
benefit of others. Usually I put the URL, but failed to in this case - my
bad. Here is is for the reference of others.

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items