ExcelBanter - Comparing to Arrays

wrote:

I'll answer 2 first - I'm on xl2000

As for the first item - the two arrays that I'm trying to compare - my
array5 and my array6 are of 20,000-30,000 elmenets in array6 and
30,000-40,000 elements in array5. To make sure that it was even moving
- I set up an Application.StatusBar line that would show me increments
of 1% complete. After about 30 minutes, it was about 8-10% complete. I
ended up letting it run over night and it did complete, but I'm willing
to bet based on scaling up what I was willing to watch that it took 5
hours.

In xl2000 the following ran in a little over 2 minutes on my machine. It
requires the availability to your workbook of the ArrayTranspose
function from the freelydownloadable file at
http://home.pacbell.net/beban. I don't have a later version of Excel,
but I suspect the time can be further reduced in them by use of
something like the commented out lines (with a little additional code to
collect the x data), which then doesn't require the add-in function,
instead of the four operative lines between the commented out stuff.

Sub Test12345()
Dim arr1(1 To 25000)
Dim arr2(1 To 25000)
For i = 1 To 25000
arr1(i) = i
arr2(i) = 3 * i
Next
starttime = Now()
'Range("A1:A5460").Value = Application.Transpose(arr1)
'Range("B1:B5460").Value = Application.Transpose(arr2)
Range("A1:A25000").Value = ArrayTranspose(arr1)
Range("B1:B25000").Value = ArrayTranspose(arr2)
Range("C1").Formula = "=COUNTIF(b$1:b$25000,a1)"
Range("C1").AutoFill Destination:=Range("C1:C25000")
'For Each Elem In arr1
'x = Application.Match(Elem, arr2, 0)
'Next
Debug.Print (Now() - starttime) * 86400
arr3 = Range("c1:c25000")
End Sub

Alan Beban