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
|