Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fast and simple way to match up arrays
What would be a fast and simple way to match up 2-D arrays with a 1-D array?
The main thing is speed, far less important is simplicity. These 2-D arrays come from ADO Recordsets. The matching up will happen on the numbers in the 1-D array and the numbers in the first column of the 2-D arrays. All these numbers are integer numbers. The 1-D array holds all the ID numbers and the 2-array a subset of these ID numbers. Both arrays are sorted ascending on these ID numbers. So for example: array1 1 2 3 4 5 6 array2 2, Apple 3, Pear 4, Orange resulting array: 1, 2, Apple 3, Pear 4, Orange 5, 6, Now I have made a Sub that does this and I think it is quite fast (matching up 2 arrays of about 25000 rows in about 70 milliseconds) but it is a bit complex. I have tried a completely different method involving running SQL on text files. This is very much simpler, but much slower. My first attempt was with the Matchup function, but that is even slower. I am probably not going to improve on my current method, but just wonder if anybody had any bright ideas about this problem. Thanks for any advice. RBS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fast and simple way to match up arrays
Because the ID numbers are unique and sorted this can be done quite simple
like this code fragment: 'arr1 is 1-based 'arr2 is 0-based 'arr3 is 1-based 'SC is a variable starting column in arr3 For r2 = 0 To RC2 - 1 For r1 = r1F To RC1 If arr1(r1) = arr2(r2, 0) Then For c = 1 To CC2 arr3(r1, SC + c - 1) = arr2(r2, c) Next r1F = r1 + 1 Exit For End If If arr1(r1) arr2(r2, 0) Then Exit For End If Next Next And I don't think this can be done much faster, so forget about this. RBS "RB Smissaert" wrote in message ... What would be a fast and simple way to match up 2-D arrays with a 1-D array? The main thing is speed, far less important is simplicity. These 2-D arrays come from ADO Recordsets. The matching up will happen on the numbers in the 1-D array and the numbers in the first column of the 2-D arrays. All these numbers are integer numbers. The 1-D array holds all the ID numbers and the 2-array a subset of these ID numbers. Both arrays are sorted ascending on these ID numbers. So for example: array1 1 2 3 4 5 6 array2 2, Apple 3, Pear 4, Orange resulting array: 1, 2, Apple 3, Pear 4, Orange 5, 6, Now I have made a Sub that does this and I think it is quite fast (matching up 2 arrays of about 25000 rows in about 70 milliseconds) but it is a bit complex. I have tried a completely different method involving running SQL on text files. This is very much simpler, but much slower. My first attempt was with the Matchup function, but that is even slower. I am probably not going to improve on my current method, but just wonder if anybody had any bright ideas about this problem. Thanks for any advice. RBS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fast and simple way to match up arrays
If you're familiar with ADO, there might be a way to write your
information to seperate datatables in a dataset, then use a SQL statement. I would guess probably pretty easy, and quick to boot. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH Function - 2 Dimension Arrays | Excel Worksheet Functions | |||
SIMPLE QUESTION need fast answer please | Excel Worksheet Functions | |||
MATCH being sent two arrays | Excel Worksheet Functions | |||
MATCH, INDEX and VLOOKUP with Arrays | Excel Programming | |||
Simple arrays with If statements | Excel Programming |