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,COLU MNS(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(SUMPROD UCT(LARGE(IF(INDEX(Data
,RowIdx,0)=G$5,G$5+1,0),Array1),Array3),G$5+1,G$5) ,"")

Hope this helps!

In article <5ff6c3193283c@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

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

Scenario:
Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

Thanks
Sam