Finding a series of values within an Array

M

marston.gould

I have a two-dimensional array, sorted along one dimension:

e.g.

array(n,1) array(n,2)
10 10020
10 20127
10 30568
11 09654
11 21678
11 54689
12 65894
..
..
99 11111

I want to quickly find only those rows (n) within the array
that match a particular value so that I can limit some looping to only
that area of the array. For instance, let's say I was able to find out
that for the value "11", the lower bound is 4 (if n starts at 1) and
the upper bound is 6.

Any suggestion?
 
G

GJones

Hi Marston;

My hack is that you could either (1) put the information
into a sheet and then use the pivot table object or (2) it
really seems like a databasing need so put it into Sequel
and then query it if you can. On other option is that you
could write it to a CSV file and then use a dynamic DSN to
execute a sequel statement against it using a text driver.

Others may have better ideas,

Thanks,

Greg
 
T

Tom Ogilvy

whatever actually provided that information would need to loop or search.
As far as I know, there is nothing that does, so you need to loop or search
yourself. If you can count on the searched column being sorted, then use a
binary search

check if it is below the lowest value or higher than the upper value, if so
quit.
go to the center of the array,
is it equal to the value: done
is it less than the value
repeat for the lower part of the array
is it greater than the value
repeat for the upper part of the array

now that you have found the value, you can loop down and up to find the
extent of the value


If it is going to be a repetitive process you could preprocess the array to
gather that information in another array.
 
A

Alan Beban

I have a two-dimensional array, sorted along one dimension:

e.g.

array(n,1) array(n,2)
10 10020
10 20127
10 30568
11 09654
11 21678
11 54689
12 65894
.
.
99 11111

I want to quickly find only those rows (n) within the array
that match a particular value so that I can limit some looping to only
that area of the array. For instance, let's say I was able to find out
that for the value "11", the lower bound is 4 (if n starts at 1) and
the upper bound is 6.

Any suggestion?
Click to expand...
lb = Application.Match(11, Application.Index(arr, 0, 1), 0)
ub = Application.Match(11, Application.Index(arr, 0, 1))

Or, in xl2000 and earlier versions if the array has more than 5461
elements and you use the array functions from my web site

lb = Application.Match(11, ColumnVector(arr,1), 0)
ub = Application.Match(11, ColumnVector(arr, 1))

Alan Beban
 
Top