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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1