Removing duplicate values from an array

[Nick Coe \(UK\)]

A2k WinXP all service packs etc applied.

Can anyone think of a better way to eliminate duplicate
values from an array variable?

I've resorted to storing the array values in a table then
doing a SELECT DISTINCT to get what I want. It works fine
but I can't help feeling there ought to be a more elegant or
efficient way of doing it.

 

[Nikos Yannacopoulos]

Nick,

In theory, I suppose you could use a second array, and populate it by
scanning throught the first one and checking for duplicates, by means of two
nested loops... that is, declare a second array of equal size, put in the
first value, then read the next value from the original array, loop through
the values already in the second array, and if no match then store that one
, and so on... now whether that would be more efficient or not is a valid
question! It depends on the size of the original array, so for a small size
(10 or 20?) it would probably be so fats you wouldn't know it's running, but
if you're talking of hundreds or thousands, my guess is you're much better
off with the temp table. Also, the second array will be partly populated if
there are duplicate values in the original one, so you need to take that
into account when you use it.
Personally I would go for the second array / nested loop option if the
original array size permits, I don't like temp tables anymore than the next
guy.

HTH,
Nikos

 

[solex]

Nick,

How are you populating the array? If you are populating through code you
might want to use a collection and set your index equal to the value of the
unique index. Collection indexes do not allow duplicates.

In addition you could write a simple function that test for the existance of
a value in your array before inserting.

Regards,
Dan

 

[Nick Coe \(UK\)]

Nikos,

Many thanks for that. I was beginning to think that way and
had started to code it up - it got messy because I've got
two one dimensional dynamic arrays to concatenate and check
or check individually. It got very messy...
Then Dan (solex's) message came through... Collections! Now
they're the bunnies... )

See my reply to Dan..

 

[Nick Coe \(UK\)]

Dan,

Many thanks. I was hacking away looping through arrays when
your message came through.

Collections... A quick scan of the ADH and it was done!!

Need to error trap for Err 457 when a duplicate is added
otherwise brilliant!

Code for anyone else who's interested -

straCHID is the array in question
lngCHID is effectively UBound(straCHID) though set by other
means

Dim colIDs As Collection, lngX as Long
Set colIDs = New Collection

For lngX = 0 To lngCHID - 1
colIDs.Add straCHID(lngX), straCHID(lngX)
Next lngX

For lngX = 1 To colIDs.Count
Debug.Print colIDs(lngX)
Next lngX

Set colIDs = Nothing

Thanks again...
--
Nick Coe (UK)
www.alphacos.co.uk

---

Nick,

How are you populating the array? If you are populating through code you
might want to use a collection and set your index equal to the value of the
unique index. Collection indexes do not allow duplicates.

In addition you could write a simple function that test for the existance of
a value in your array before inserting.

Regards,
Dan