Assumptions...
P19:V48 contains the data (referred to by the dynamic named range 'Data')
AA17 contains the criteria
Defined Names...
Insert Name Define
Name: Col
Refers to:
=INDEX(Data,0,ROWS(AA$19:AA19))
Click Add
Name: Array1
Refers to:
=OFFSET(Col,,,ROWS(Col)-$AA$17-2)
Click Add
Name: Array2
Refers to:
=N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))-1,0,1))
Click Add
Name: Array3
Refers to:
=N(OFFSET(Col,ROW(INDIRECT("1:"&ROWS(Col)-$AA$17-2))+TRANSPOSE(ROW(INDIRE
CT("1:"&$AA$17+2))-1),,1))
Click Add
Name: Array4
Refers to:
=MMULT(IF(Array20,IF(Array3=TRANSPOSE(MOD(ROW(IND IRECT("1:"&$AA$17+2))-1
,$AA$17+1)),1,0),0),ROW(INDIRECT("1:"&$AA$17+2))^0 )
Click Ok
Formulas...
AA19, copied down:
=SUMPRODUCT(--(Array4=$AA$17+2))
AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down:
=IF(COLUMNS($AB19:AB19)<=$AA19,INDEX(Array1,SMALL( IF(Array4=$AA$17+2,ROW(
Array1)-MIN(ROW(Array1))+1),COLUMNS($AB19:AB19))),"")
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article <9f4f6bab1feb6@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:
Hi All,
I am using Windows Excel 2003.
If possible, I would like a single and flexible formula to provide the
results of the scenario listed below.
Scenario:
I have a dynamic named range called Data. Data spans 7 columns
and many
rows.
Row 17 is header.
Row 18 may have values in some columns.
Row 19, column P is start of Data.
The oldest data is at the top, starting in row 19.
I would like a specific sequence / pattern matched and then 2 values returned
to the same cell using a comma to separate them. The paired, matched values
should be returned across a single row until there are no more instances to
match.
The pattern matching is NOT single column based but spans all 7 columns.
That is, the criteria to be found must start with the oldest instance of the
criteria (working from the top row 19 and down) which can be in any column of
Data, and then the 2nd oldest which can again be in any column, 3rd
oldest
which can again be in any column and so on.
PATTERN TO MATCH starting from top of dynamic range Data:
Step 1. Any value -
Step 2. 0 value this must be on next row below value in Step 1.
Step 3. 1st instance of criteria *offset 1 row & using criteria value count
down row.
Step 4. 0 value this must be on next row below value in Step 3.
Start process again
Step 1. Any value -
Step 2. 0 value this must be on next row below value in Step 1.
Step 3. 2nd instance of criteria *offset 1 row & using criteria value count
down row.
Step 4. 0 value this must be on next row below value in Step 3.
Start process again
Step 1. Any value -
Step 2. 0 value this must be on next row below value in Step 1.
Step 3. 3rd instance of criteria *offset 1 row & using criteria value count
down row.
Step 4. 0 value this must be on next row below value in Step 3.
Start process again
Step 1. Any value -
Step 2. 0 value this must be on next row below value in Step 1.
Step 3. 4th instance of criteria *offset 1 row & using criteria value count
down row.
Step 4. 0 value this must be on next row below value in Step 3.
Continue pattern matching process until no more criteria can be found to
return across a single row.
The 1st instance must be the oldest instance of the criteria starting
from
the top of my range Data. The 2nd instance is 2nd oldest instance of
the
criteria. The 3rd instance is the 3rd oldest instance and so forth.
* offset 1 row & using criteria value count down row: Once youve counted
down x number of rows based on the value of the criteria, the cell that you
reach should equal the value of the criteria.
Any value: Is literally any value that is found above the 0 value in Step2.
In the sample data the criteria Im looking for is 4 (it will vary) and the
pattern should be matched as explained above.
Sample Data Expected Results:
2,4 3,4 2,4 11,4 3,4
Sample Data Layout:
RowNo ColP ColQ ColR ColS ColT ColU ColV
19 1 1 0 1 0 1 1
20 2 2 1 2 1 0 2
21 3 3 2 3 2 1 0
22 4 4 3 0 3 2 1
23 5 5 0 1 0 3 2
24 6 0 0 2 1 4 3
25 7 1 1 3 2 5 4
26 8 0 2 4 0 6 0
27 9 1 3 0 1 7 0
28 10 2 4 1 2 8 1
29 11 3 5 0 3 9 2
30 0 4 6 1 4 10 0
31 1 5 7 2 5 11 1
32 2 6 8 3 6 0 2
33 3 7 9 0 0 1 3
34 4 8 10 1 1 2 4
35 5 0 11 2 2 3 0
36 6 1 12 3 3 4 1
37 7 2 13 0 4 0 2
38 8 3 14 1 5 1 3
39 9 4 15 2 6 2 4
40 0 5 16 3 7 0 5
41 1 6 17 0 8 1 0
42 2 7 18 1 9 2 1
43 3 8 19 2 0 3 2
44 4 9 20 3 1 4 0
45 5 10 21 4 2 5 1
46 6 11 22 0 3 6 2
47 0 12 23 1 4 7 3
48 1 13 24 2 5 8 4
Workings for Expected Results:
1st paired match is Column V, rows 19-26 return 2,4
2nd paired match is Column S, rows 19-27 return 3,4
3rd paired match is Column V, rows 28-35 return 2,4
4th paired match is Column U, rows 21-37 return 11,4
5th paired match is Column S, rows 38-46 return 3,4
NB: Column V, rows 42-48 is NOT a paired match because there is no
0
(zero) below the criteria 4 in row 48.
Your help is very much appreciated.
Thanks,
Sam