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

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

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!