Hi!

Here's one way:

Use a helper column and add it to your dynamic range. This would be column
S.

Enter this formula is S76 and copy down to the end of your data:

=REPT(Sheet2!G$5,COUNTIF(I76:R76,Sheet2!G$5))

On the "new" sheet enter this formula in G7 as an array using the key combo
of CTRL,SHIFT,ENTER:

=INDEX(Data,SMALL(IF(INDEX(Data,,11)<>"",ROW(Data)-76+1),ROWS($1:1)),11)

Copy down until you get #NUM! errors meaning all the matching data has been
exhausted.

Biff

"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:5ff6c3193283c@uwe...
> 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
>
> --
> Message posted via http://www.officekb.com