Match Multiple Criteria and Return values positioned above the zero

[Sam via OfficeKB.com]

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 you’ve 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 I’m 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

 

[Domenic]

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(Array2>0,IF(Array3=TRANSPOSE(MOD(ROW(INDIRECT("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))),"")

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for reply, and all your time and assistance.

Your solution does provide an answer in part. The outstanding part is
somehow trying to list the returned values from earliest to most recent. I'm
still trying to see if I can somehow list the returned values returned from
earliest to most recent.

Thank you for all your help.
If anything else comes to mind very much appreciated.

Cheers,
Sam

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(Array2>0,IF(Array3=TRANSPOSE(MOD(ROW(INDIRECT("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))),"")

Hi All,

[quoted text clipped - 123 lines]

Thanks,
Sam

 

[Domenic]

The outstanding part is

somehow trying to list the returned values from earliest to most recent.

Somewhat unclear... Can you provide the expected result for the sample
data?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Hi Domenic,

Thank you very much for reply, and all your time and assistance.

Your solution does provide an answer in part. The outstanding part is
somehow trying to list the returned values from earliest to most recent. I'm
still trying to see if I can somehow list the returned values returned from
earliest to most recent.

Thank you for all your help.
If anything else comes to mind very much appreciated.

Cheers,
Sam

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(Array2>0,IF(Array3=TRANSPOSE(MOD(ROW(INDIRECT("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))),"")

Hi All,

[quoted text clipped - 123 lines]

Thanks,
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for reply.

Somewhat unclear... Can you provide the expected result for the sample
data?

Sample Data Expected Results:
2,4 3,4 2,4 11,4 3,4

Using the row numbers to ascertain oldest and most recent criteria data.

The main part of the solution is to list the expected results in order of the
earliest (oldest) result to the most recent. I thought about somehow using
the row number after each criteria value; that would be the row below the
criteria where the “0†resides. Therefore, per the sample data, we would be
looking at row numbers 27, 46, 37, 26, and 35. I would then use those row
numbers in ascending order to order the returned values. However, using your
formula if, I inserted columns before or rows beneath each returned value, it
would interfere with the fill across and fill down of the formula. So I
can’t see a way to do that.

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

 

[Domenic]

First, replace the formula for AB19 with the following...

AB19, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(COLUMNS($AB19:AB19)<=$AA19,SMALL(IF(Array4=$AA$17+2,ROW(Array1)-MIN(R
OW(Array1))+1),COLUMNS($AB19:AB19)),"")

Then try...

AA28:

=SUM(AA19:AA25)

AB28, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($AB28:AB28)<=$AA28,INDEX(Data,SMALL($AB$19:$AK$25,COLUMNS($AB
28:AB28)),RIGHT(SMALL(IF($AB$19:$AK$25<>"",$AB$19:$AK$25*10^7+ROW($AB$19:
$AK$25)-ROW($AB$19)+1),COLUMNS($AB28:AB28)),7))&","&$AA$17,"")

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you ever so much for all your time and assistance. Your solution does
provide me with the required results. Absolutely Brilliant!

Cheers,
Sam

First, replace the formula for AB19 with the following...