array search

[Luke]

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).

I hope this is clear enough
Thank you,
Luke

 

[Shane Devenshire]

Hi,

You might want to revisit this description. I for one have a hard time
following it and can understand what you mean by
"match D17:F19 with P26:R26 (not D25:F25)"

What do you mean by match are 9 cell range (D17:F19) with a 3 cell range
P26:R26 ?

 

[Max]

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]

Shane,
Sorry I did leave that out. I am using a much larger matrix and forgot to
mention there are data beyond P25:R25.

Essentially for this example, P25:R25 are the cells to acquire the
horizontal and vertical count, that in turn finds the range/array D17:F19.

Then, Any of the cells in P26:R26 that match any of the cells in D17:F19
would be displayed at a defined cell range... Say for this example X26:Z26
(Ooops, another tidbit I left out).
I Hope this helps.
Luke

 

[Luke]

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 D1719.
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.
(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]

Maybe this might satisfy your complex intents ..
In S26:
=IF(COUNTIF(OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3),P26),P26,"")
Copy S26 to U26. This checks each element in P26:R26 vs the array D17:F19
and returns match results in the same order, ie in your sample, it'll return
as: 1, 6, 4.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

Tinkered with 2 other result variations for you to try out over there

This will return the matched results in ascending order from right to left
Array-entered in S26, copied to U26
=SMALL(IF(COUNTIF($P26:$R26,OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3)>0,COUNTIF($P26:$R26,OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3)),COLUMNS($A:A))

Above returns #NUM! if element in P26:R26 is unmatched

This will return the matched results in descending order from right to left
Array-entered in S26, copied to U26
=LARGE(COUNTIF($P26:$R26,OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3),COLUMNS($A:A))

ABove returns zero if element in P26:R26 is unmatched

NB: Array-entered means press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Luke]

Very nice! I'm not sure exactly how that works but I did get the results.
However if I fill S26:U26 up or down the results are some what off base. Any
Ideas?

I will work with this to see if I can understand it better.

Nice Job Max! I will look back at this in the morning to catch any replies.
Luke

 

[Max]

Luke,

I've posted another 2 variations using array SMALL/LARGE to return results
in ascending/descending order to play around with

However if I fill S26:U26 up or down the results are some what off base

The crux flexi-capture of the array is via the OFFSET, which construct is
explained in my 1st response. Maybe it still needs some further refinement, I
don't know, as I've made some interpretations/assumptions based on your
descripts on the params. Tinker with the OFFSET's params, that's the key
here. In the formula bar, select just the OFFSET term, then press F9 to see
the result array generated: {...} - reconcile with the matrix on the sheet
that it is supposed to capture. Press Esc to revert. If the OFFSET's capture
is not correct, then of course the downstream matching with cols P to R will
yield meaningless results.

Btw, pl mark all responses which contribute to resolving your complex issue
by clicking the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---