Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing to Arrays
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing 2 arrays | Excel Worksheet Functions | |||
Comparing Arrays | Excel Discussion (Misc queries) | |||
Comparing Arrays | Excel Worksheet Functions | |||
Comparing to Arrays | Excel Programming | |||
Comparing to Arrays | Excel Programming |