fast and simple way to match up arrays

[RB Smissaert]

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

 

[RB Smissaert]

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

 

[Claud Balls]

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.