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($A B$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 ,"")
--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
In article <9fa7dc6e46aad@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:
Hi Domenic,
Thank you for reply.
Domenic wrote:
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?
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
cant 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