Alternative to Application.Match

[marston.gould]

As I've recently found out - I'm trying to use Application.Match on an
array that is too long. I'm looking for an alternative and would like
some suggestions.

Here's the situation -

I have two arrays: arr1 and arr2, both are two dimensional
arr1(n,1) holds the string values I need to match and I need to match
it within the string values of arr2(m,1). Both arr1 and arr2 are sorted
by the first column. All of the values within arr1 are within arr2 at
least once and sometimes more than once. In both cases, there is at
least 1 if not more situations where the value of arr1(n,1) or
arr2(m,1) are equal to "000" - and they generally make up a large
section of both arr1 and arr2 and in both cases, I don't need to work
on either array elements when they hold this value (e.g. I'd like to
skip them)

Here is what I have:

h = 0
j = 0
i = UBound(arr2,1)
Do Until h = 1
j = j + 1
If (arr2(j,1) > "000" and h = 0 Then
k = j
h = 1
End If
Loop
For i = 1 to UBound(arr1,1)
h = 0
If arr(i,1) <> "000" Then
For j = k to UBound(arr2,1)
If arr2(j,1) > arr1(i,1) Then
j = UBound(arr2,1)
Else
If arr2(j,1) = arr1(i,1) Then
..
..
..
(Do some other things)
..
..
..
If h = 0 Then
h = 1
k = j
End If
End If
End If
Next j
End If
Next i

The reason for the whole h = 0 and h = 1 thing is that in the cases
where there are multiple similar values in arr1, I don't want to reset
the starting point for the j = k to Ubound(arr,2).

The only problem with this is that its taking forever to run....
Any thoughts?

 

[Bernie Deitrick]

Marston,

Use a binary search - there are plenty of example of binary search
algorithms in the archives, so google away.

HTH,
Bernie
MS Excel MVP

 

[Stephen Bullen]

Hi Marston

I have two arrays: arr1 and arr2, both are two dimensional
arr1(n,1) holds the string values I need to match and I need to match
it within the string values of arr2(m,1). Both arr1 and arr2 are sorted
by the first column. All of the values within arr1 are within arr2 at
least once and sometimes more than once. In both cases, there is at
least 1 if not more situations where the value of arr1(n,1) or
arr2(m,1) are equal to "000" - and they generally make up a large
section of both arr1 and arr2 and in both cases, I don't need to work
on either array elements when they hold this value (e.g. I'd like to
skip them)

As they're both sorted with arr1 being a unique list, I'd loop through
them both in a single pass (untested):

Dim lIdx1 As Long
Dim lIdx2 As Long

lIdx1 = LBound(arr1)
lIdx2 = LBound(arr2)

Do Until lIdx1 > Ubound(arr1) Or lIdx2 > Ubound(arr2)

'Compare the two array items
Select Case StrComp(arr1(lIdx1,1), arr2(lIdx2,1))
Case 0
'They're the same
If arr1(lIdx1,1) <> "000" Then
'Process them
End If

'Go on to the next in arr2
lIdx2 = lIdx2 + 1
Case 1
'arr1 > arr2, so move to next in arr2
lIdx2 = lIdx2 + 1
Case -1
'arr1 < arr2, so move to next in arr1
lIdx1 = lIdx1 +1
End Select
Loop


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie