Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Match
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Match
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 " wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Match
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup - find an alternative approximate match | Excel Worksheet Functions | |||
Alternative add-on application to VLOOKUPS command? | Excel Discussion (Misc queries) | |||
application.match | Excel Discussion (Misc queries) | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming |