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