Hi Domenic,

Thank you so much. Absolutely Brilliant!!

Is there any chance that the Results returned to Sheet2 can mimic the Row
position of the Source Data?

Using the Sample Data Layout;
Row 76 translates to Row 7, 1st Row of Results - Sheet2.
Row 77 translates to Row 8, 2nd Row of Results - Sheet2.
Row 78 translates to Row 9, 3rd Row of Results - Sheet2.

So, if I was looking for Criterion 3, the first Result returned should be
from Row 78 to Sheet2 Row 9, the 3rd Row on Sheet2 (Results). All Results to
be returned to their corresponding Row Position on Sheet2.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Cheers,
Sam

Domenic wrote:
First, define the following names/references...
Select G7
Insert Name Define
Name: Array1
Refers to:
=ROW(INDEX(Sheet2!$A:$A,1):INDEX(Sheet2!$A:$A,COL UMNS(Data)))
Click Add

Name: Array2
Refers to:

=(MMULT(--(Data=Sheet2!G$5),TRANSPOSE(COLUMN(Data)^0))0)+0
Click Add

Name: Array3
Refers to:
=10^(Array1*LEN(Sheet2!G$5+1))/10^LEN(Sheet2!G$5+1)
Click Add

Name: RowIdx
Refers to:
=SMALL(IF(MMULT(--(Data=Sheet2!G$5),TRANSPOSE(COLUMN(Data)^0)),ROW(D ata)-
MIN(ROW(Data))+1),ROWS(Sheet2!$G$7:$G7))
Click Ok

Note that I've assumed that Sheet2 will contain the results data.
Change the sheet reference accordingly. Now, try the following formula,
which needs to be confirmed with CONTROL+SHIFT+ENTER...

G7, copied down and across:
=IF(ROWS($G$7:$G7)<=SUM(Array2),SUBSTITUTE(SUMPRO DUCT(LARGE(IF(INDEX(Data
,RowIdx,0)=G$5,G$5+1,0),Array1),Array3),G$5+1,G$5 ),"")

Hope this helps!

Hi All,

[quoted text clipped - 34 lines]
Thanks
Sam

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