Max, Example below G15:O26 are left blank to save space in this reply. All
cells have digits in reality with no blank cells.
In D17:F19 = 943, 178, 206 with respect to each cell.
In P26:R26 = 164 with respect to each cell. Note that numbers 1, 6, & 4
exist in D17:D19.
In S26:U26 = 146 (Result cells)
P25:R25 =334:
P25=3 means move right to left from P25, 12 columns, or, 4 sets of three
columns. Left horizontal movement, as it were, to D25:F25.
"ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR"
(Q25=3 + R25=4) = 7 means move up vertically 7 cells to D18:F18 from D25:F25.
Then compare P26, Q26 & R26 to the array D17:F19.
OR, If that is not possible then I could live with just finding and
comparing P26, Q26 & R26 with D18, E18, & F18 placing the results in S26,
T26, & U26 respectfully.
A B C D E F..G,H,I J,K,L M,N,O P Q R S T U
15 2 1 0 1 1 5 7 6 9
16 5 5 5 5 2 5 8 4 1
17 3 6 0 9 4 3 0 4 4
18 5 1 2 1 7 8 9 9 4
19 4 2 4 2 0 6 1 8 7
20 0 4 1 4 3 0 3 8 1
21 3 6 7 5 2 7 6 5 4
22 2 5 2 1 1 9 7 1 1
23 5 8 6 4 7 0 5 4 9
24 7 7 9 3 3 7 4 8 2
25 7 9 7 4 6 2 3 3 4
26 5 9 4 6 7 2 1 6 4 1 4 6
27 2 8 8 4 0 2 5 0 9
I hope this helps or at leaset makes better sense.
Luke
"Max" wrote:
Some other thoughts ..
... a formula find D18:F18 based on the earlier parameters,
then match D17:F19 with P26:R26 (not D25:F25)
Maybe you're looking to fashion something like this,
in say S25:
=SUMPRODUCT(--ISNUMBER(MATCH(OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3),P26:R26,0)))
where this term: OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3)
will return the required 3R x 3C matrix: D17:F19
based on the parameters in P25:R25
(the A25 is just an anchor point on the same row)
The row param: -(SUM(Q25:R25)+1)
will locate the top left cell of the 3x3 matrix
vertically up from the anchor A25,
with an arithmetic adjustment of +1 to SUM(Q25:R25),
since SUM(Q25:R25) locates the row for "D18:F18"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Luke" wrote:
Thanks all for helping. Here are the details and I'll ask the question in a
minute.
I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all
numeric and no blank cells.
To keep it simple let us say that columns PQR is the extent of the sets.
Column P is the basis for a horizontal count.
Columns Q & R are added together and their total will be the Vertical count.
The horizontal count starts from P and counts from right to left as follows
ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR.
IF P25=3, Q25=3, R25=4
The count would be horizontal from P25 to D25:F25 (set #3).
The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical
count back to D18:F18.
With that said, is it possible to have a formula find D18:F18 based on the
earlier parameters, then match D17:F19 with P26:R26 (not D25:F25).