Duplicates in array (Access 2000)

Topic

New Reply

Hi,
Can anyone help? I have an array containing a variable number of codes:

(21, 23, 4, 21)

I want to take this array and deduplicate it, ie returning:

(21, 23, 4)

I am going round and round with this, with my only working solution so far being outputting the array contents to a temporary table, then reinserting them into the array. Can anyone please help point me in a better way?

Thanks,
J

Reply | Quote

Replies

Your temp table will probably be the fastest, as the SELECT DISTINCT operator will eliminate any duplicates and return the values in order.
However, you could try either changing the array to a parameter array, saving it to a string variable, and using LEN and INSTR to locate and delete duplicates, or writing a couple of nested loops in code.

Reply | Quote

Another way to handle it:

Function RemoveDups(arr() As Variant) As Variant
Dim col As New Collection
Dim i As Integer
Dim arrNew() As Variant
On Error Resume Next
For i = LBound(arr) To UBound(arr)
col.Add arr(i), CStr(arr(i))
Next i
ReDim arrNew(1 To col.Count)
For i = 1 To col.Count
arrNew(i) = col(i)
Next i
RemoveDups = arrNew
Set col = Nothing
End Function

Sub TestRemoveDups()
Dim arrIn() As Variant
Dim arrOut() As Variant
Dim i As Integer
ReDim arrIn(1 To 4)
arrIn(1) = 21
arrIn(2) = 23
arrIn(3) = 4
arrIn(4) = 21
arrOut = RemoveDups(arrIn)
For i = 1 To UBound(arrOut)
Debug.Print i, arrOut(i)
Next i
End Sub

Reply | Quote

What piece of your code Hans actually deletes the dups?

Reply | Quote

The code doesn’t actually delete anything. It adds the elements of the original array to a collection. Collections don’t allow two elements to have the same key. Normally, trying to add a duplicate would cause an error, but this is silently suppressed by the line On Error Resume Next. So at the end, the collection object contains the unique entries from the array. These are then written to the output array.

Reply | Quote

Thanks for the explanation, i didn’t know that collections don’t allow dups.

I learn something every day, thanks again Hans.

Reply | Quote

This is why I’m a daily Lounge browser.

Reply | Quote