search a matrix for values

[Phil Newman]

OK, this is a bit of a tricky problem....

i have a 7*7 matrix in excel, which is full of numbers...

i want to search in only half the matrix, where half is from cell1-1,
to cell 7-1 (i.e. top left to bottom right). what i want to search is
for numbers a certain value, say X.

what i then want to do is tablulate these numbers in one column, and
then tablue late their position as a reference to the matrix (i.e.
their matrix position).

how on earth can i do this?

Phil

 

[Bob Phillips]

I have assumed that the matrix is in A1:G7

In A10 I entered

=INDEX($A$1:$H$7,ROW(A1),ROW(A1))

and copied down to A16

In B10 I added

=CELL("address",INDEX(OFFSET($A$1,,ROW(A1)-1,7,1),MATCH(A10,OFFSET($A$1,,ROW
(A1)-1,7,1),0)))

and copied down

Just adjust to your ranges.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I should have added that where I use A1, that should remain, don't adjust
that, use A1 for the first, A2 for the second etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Phil Newman]

Hi,

I'm not sure if the code given gives me the right answers, but i can't
get them to work anyway, for the second column, i get N/A which is odd.

Phil

 

[Phil Newman]

sorry, i mis-typed the original post, i want to search for numbers
ABOVE a certain value

Phil

 

[Herbert Seidenberg]

I assume you want to find numbers bigger than a target value
in the lower diagonal half of the matrix, including the diagonal
center, a total of 28 cells.
I assume that the matrix has unique values and
horizontal and vertical headers from 1 to 7, like this:
1 2 3 4 5 6 7
1 87 50 70 31 64 61 18
2 83 69 76 34 36 17 32
3 41 73 65 52 74 80 63
4 56 85 79 89 72 88 98
5 97 51 93 53 67 91 49
6 71 99 45 11 48 58 59
7 84 46 33 57 37 66 77
Name the 7x7 matrix ArrayA,
the column header Colm and
the row header Roam
Create another 7x7 matrix named ArrayB with this array formula:
=ArrayA*(ArrayA>Target)*(Roam>=Colm)
Assuming Target=55, ArrayB will look like this:
87 0 0 0 0 0 0
83 69 0 0 0 0 0
0 73 65 0 0 0 0
56 85 79 89 0 0 0
97 0 93 0 67 0 0
71 99 0 0 0 58 0
84 0 0 57 0 66 77
To list the numbers in a column, create a template like this:
Seq ListA RowA ColumnA
1 99 6 2
2 97 5 1
3 93 5 3
4 89 4 4
5 87 1 1
6 85 4 2
7 84 7 1
8 83 2 1
9 79 4 3
10 77 7 7
11 73 3 2
12 71 6 1
13 69 2 2
14 67 5 5
15 66 7 6
16 65 3 3
17 58 6 6
18 57 7 4
19 56 4 1
20 0 0 0
21 0 0 0
22 0 0 0
23 0 0 0
24 0 0 0
25 0 0 0
26 0 0 0
27 0 0 0
28 0 0 0
Name the columns with the suggested headers.
Seq is just a sequence of numbers.
ListA, RowA and ColumnA have these formulas:
=LARGE(ArrayB,Seq)
=SUMPRODUCT((ArrayA=INDEX(ListA,Seq))*Roam)
=SUMPRODUCT((ArrayA=INDEX(ListA,Seq))*Colm)

 

[Leo Heuser]

"Phil Newman" <[email protected]> skrev i en meddelelse sorry, i mis-typed the original post, i want to search for numbers
ABOVE a certain value

Phil

Hi Phil

Assuming X in B2 and no duplicates in the matrix, here is one
way to do it. The matrix can be any *square* size, named or with
cell references. For duplicates only one address will be displayed.
Depending on the placement in the matrix of the value in question,
this address may be wrong.

In e.g. P5 enter this array formula:

=LARGE(IF((MIN(ROW(Matrix))+ROWS(Matrix)-1-ROW(Matrix)<=MIN(COLUMN(Matrix))-COLUMN(Matrix)+COLUMNS(Matrix)-1)*(Matrix>$B$2),Matrix),ROW()-ROW($P$5)+1)

Enter with <Shift><Ctrl><Enter> instead of <Enter>, also if edited
later. If done properly, Excel will display the formula in the formula
bar enclosed in braces { } Don't enter these braces yourself.

ROW($P$5) must always reflect the starting cell, since
ROW()-ROW($P$5) functions as a counter.

In Q5 enter this array formula:

=ADDRESS(MAX((P5=Matrix)*ROW(Matrix)),MAX((P5=Matrix)*COLUMN(Matrix)))

Again with <Shift><Ctrl><Enter>

Select P5:Q5 and copy down with the fill handle (the little
square in the lower right corner of the selection).
The number of cells to copy down is (N*(N-1))/2+N-1, where
N is the side in the matrix (in this situation 7).

The values in P5 and down are displayed in descending order.

 

[Leo Heuser]

Hi Phil

Assuming X in B2 and no duplicates in the matrix, here is one
way to do it. The matrix can be any *square* size, named or with
cell references. For duplicates only one address will be displayed.
Depending on the placement in the matrix of the value in question,
this address may be wrong.

In e.g. P5 enter this array formula:

=LARGE(IF((MIN(ROW(Matrix))+ROWS(Matrix)-1-ROW(Matrix)<=MIN(COLUMN(Matrix))-COLUMN(Matrix)+COLUMNS(Matrix)-1)*(Matrix>$B$2),Matrix),ROW()-ROW($P$5)+1)

Enter with <Shift><Ctrl><Enter> instead of <Enter>, also if edited
later. If done properly, Excel will display the formula in the formula
bar enclosed in braces { } Don't enter these braces yourself.

ROW($P$5) must always reflect the starting cell, since
ROW()-ROW($P$5) functions as a counter.

In Q5 enter this array formula:

=ADDRESS(MAX((P5=Matrix)*ROW(Matrix)),MAX((P5=Matrix)*COLUMN(Matrix)))

Again with <Shift><Ctrl><Enter>

Select P5:Q5 and copy down with the fill handle (the little
square in the lower right corner of the selection).
The number of cells to copy down is (N*(N-1))/2+N-1, where
N is the side in the matrix (in this situation 7).

The values in P5 and down are displayed in descending order.


--
Best regards
Leo Heuser

Followup to newsgroup only please.

The various possibilities:

Lower half (incl. diagonal) use <= in the formula
Lower half (excl. diagonal) use <

Upper half (incl. diagonal) use >=
Upper half (exc. diagonal) use >

Diagonal use =

Leo

 

[Phil Newman]

Thanks very much for all your help, but i've decided to use MatLab for
my problems instead as it handles the matrix much easier.

Cheers,

Phil

 

[Leo Heuser]

Thanks very much for all your help, but i've decided to use MatLab for
my problems instead as it handles the matrix much easier.

Cheers,

Phil

You're welcome. Thanks for your feedback.

Cheers,
Leo Heuser