software

Hi All,

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

I have a Dynamic Named Range quot;Dataquot; 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 quot;Dataquot; 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 www.officekb.com

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 quot;newquot; sheet enter this formula in G7 as an array using the key combo
of CTRL,SHIFT,ENTER:

=INDEX(Data,SMALL(IF(INDEX(Data,,11)lt;gt;quot;quot;,ROW(Data)-76 1),ROWS($1:1)),11)

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

Biff

quot;Sam via OfficeKB.comquot; lt;u4102@uwegt; wrote in message
news:5ff6c3193283c@uwe...
gt; Hi All,
gt;
gt; Find Multiple instances of Numeric Criterion in Row amp; Return To a Single
gt; Column.
gt;
gt; I have a Dynamic Named Range quot;Dataquot; spanning 10 Columns and many Rows.
gt; Each Row may contain duplicates of the Numeric Criterion.
gt;
gt; I would like to find ALL instances of a specific Numeric Criterion across
gt; each single Row in the Dynamic Range quot;Dataquot; and have the Results returned
gt; to
gt; a New Sheet in a single column.
gt;
gt; NEW Sheet:
gt; The Numeric Criterion is housed in G5.
gt; The matched criterion should be returned to the New Sheet starting at G7.
gt; Duplicate instances in the same Row should ALL be returned to the same
gt; cell
gt; in Column G on the New Sheet.
gt;
gt; Sample Data Layout:
gt; Columns I J K L M N O P Q R
gt; Row No.76 1 0 1 1 0 1 1 1 0 1
gt; Row No.77 2 2 3 2 1 2 2 0 0 0
gt; Row No.78 3 3 3 3 3 0 3 0 3 0
gt;
gt; Scenario:
gt; Looking for Numeric Criterion 1 (one).
gt;
gt; Expected Results - New Sheet:
gt; Row No.7 Column G (Cell G7) 1111111
gt; Row No.8 Column G (Cell G8) 1
gt;
gt; In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should
gt; be
gt; returned to the same cell G7.
gt; In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and
gt; it
gt; should be returned to cell G8.
gt;
gt; Thanks
gt; Sam
gt;
gt; --
gt; Message posted via www.officekb.com
Hi Biff,

Thank you for reply.

Your solution does work. However, I need to find numerous different Numeric
Criterion (that will be returned to different Columns) and this will add many
extra Helper columns to the Dynamic Range quot;Dataquot;, is there another solution
or workaround possible.

Further assistance much appreciated.

Cheers,
Sam

Biff wrote:
gt;Hi!

gt;Here's one way:

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

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

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

gt;On the quot;newquot; sheet enter this formula in G7 as an array using the key combo
gt;of CTRL,SHIFT,ENTER:

gt;=INDEX(Data,SMALL(IF(INDEX(Data,,11)lt;gt;quot;quot;,ROW(Data )-76 1),ROWS($1:1)),11)

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

gt;Biff
gt;
gt;gt; Hi All,
gt;gt;
gt;[quoted text clipped - 38 lines]
gt;gt; Thanks
gt;gt; Sam

--
Message posted via www.officekb.com

Is the source data in your dynamic range 'Data' made up of only single
digits?

In article lt;5ff6c3193283c@uwegt;, quot;Sam via OfficeKB.comquot; lt;u4102@uwegt;
wrote:

gt; Hi All,
gt;
gt; Find Multiple instances of Numeric Criterion in Row amp; Return To a Single
gt; Column.
gt;
gt; I have a Dynamic Named Range quot;Dataquot; spanning 10 Columns and many Rows.
gt; Each Row may contain duplicates of the Numeric Criterion.
gt;
gt; I would like to find ALL instances of a specific Numeric Criterion across
gt; each single Row in the Dynamic Range quot;Dataquot; and have the Results returned to
gt; a New Sheet in a single column.
gt;
gt; NEW Sheet:
gt; The Numeric Criterion is housed in G5.
gt; The matched criterion should be returned to the New Sheet starting at G7.
gt; Duplicate instances in the same Row should ALL be returned to the same cell
gt; in Column G on the New Sheet.
gt;
gt; Sample Data Layout:
gt; Columns I J K L M N O P Q R
gt; Row No.76 1 0 1 1 0 1 1 1 0 1
gt; Row No.77 2 2 3 2 1 2 2 0 0 0
gt; Row No.78 3 3 3 3 3 0 3 0 3 0
gt;
gt; Scenario:
gt; Looking for Numeric Criterion 1 (one).
gt;
gt; Expected Results - New Sheet:
gt; Row No.7 Column G (Cell G7) 1111111
gt; Row No.8 Column G (Cell G8) 1
gt;
gt; In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
gt; returned to the same cell G7.
gt; In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
gt; should be returned to cell G8.
gt;
gt; Thanks
gt; Sam

I think Laurent Longre's MOREFUNC.XLL add-in has a quot;concatenate ifquot;
function that might work for this but I've never used it.

xcell05.free.fr/english/

Biff

quot;Sam via OfficeKB.comquot; lt;u4102@uwegt; wrote in message
news:5ff86677b108c@uwe...
gt; Hi Biff,
gt;
gt; Thank you for reply.
gt;
gt; Your solution does work. However, I need to find numerous different
gt; Numeric
gt; Criterion (that will be returned to different Columns) and this will add
gt; many
gt; extra Helper columns to the Dynamic Range quot;Dataquot;, is there another
gt; solution
gt; or workaround possible.
gt;
gt; Further assistance much appreciated.
gt;
gt; Cheers,
gt; Sam
gt;
gt; Biff wrote:
gt;gt;Hi!
gt;
gt;gt;Here's one way:
gt;
gt;gt;Use a helper column and add it to your dynamic range. This would be column
gt;gt;S.
gt;
gt;gt;Enter this formula is S76 and copy down to the end of your data:
gt;
gt;gt;=REPT(Sheet2!G$5,COUNTIF(I76:R76,Sheet2!G$5))
gt;
gt;gt;On the quot;newquot; sheet enter this formula in G7 as an array using the key
gt;gt;combo
gt;gt;of CTRL,SHIFT,ENTER:
gt;
gt;gt;=INDEX(Data,SMALL(IF(INDEX(Data,,11)lt;gt;quot;quot;,ROW(Dat a)-76 1),ROWS($1:1)),11)
gt;
gt;gt;Copy down until you get #NUM! errors meaning all the matching data has
gt;gt;been
gt;gt;exhausted.
gt;
gt;gt;Biff
gt;gt;
gt;gt;gt; Hi All,
gt;gt;gt;
gt;gt;[quoted text clipped - 38 lines]
gt;gt;gt; Thanks
gt;gt;gt; Sam
gt;
gt; --
gt; Message posted via www.officekb.com
Hi Domenic,

gt;Is the source data in your dynamic range 'Data' made up of only single digits?
No, it does include double digits.

Cheers,
Sam

Domenic wrote:
gt;Is the source data in your dynamic range 'Data' made up of only single
gt;digits?
gt;
gt;gt; Hi All,
gt;gt;
gt;[quoted text clipped - 34 lines]
gt;gt; Thanks
gt;gt; Sam

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

Hi Biff,

Thank you for assitance.

Cheers,
Sam

Biff wrote:
gt;I think Laurent Longre's MOREFUNC.XLL add-in has a quot;concatenate ifquot;
gt;function that might work for this but I've never used it.

gt;xcell05.free.fr/english/
gt;
gt;Biff
gt;
gt;gt; Hi Biff,
gt;gt;
gt;[quoted text clipped - 41 lines]
gt;gt;gt;gt; Thanks
gt;gt;gt;gt; Sam

--
Message posted via www.officekb.com

I can't wait to see this one!

Biff

quot;Domenicquot; gt; wrote in message
...
gt; Is the source data in your dynamic range 'Data' made up of only single
gt; digits?
gt;
gt; In article lt;5ff6c3193283c@uwegt;, quot;Sam via OfficeKB.comquot; lt;u4102@uwegt;
gt; wrote:
gt;
gt;gt; Hi All,
gt;gt;
gt;gt; Find Multiple instances of Numeric Criterion in Row amp; Return To a Single
gt;gt; Column.
gt;gt;
gt;gt; I have a Dynamic Named Range quot;Dataquot; spanning 10 Columns and many Rows.
gt;gt; Each Row may contain duplicates of the Numeric Criterion.
gt;gt;
gt;gt; I would like to find ALL instances of a specific Numeric Criterion across
gt;gt; each single Row in the Dynamic Range quot;Dataquot; and have the Results returned
gt;gt; to
gt;gt; a New Sheet in a single column.
gt;gt;
gt;gt; NEW Sheet:
gt;gt; The Numeric Criterion is housed in G5.
gt;gt; The matched criterion should be returned to the New Sheet starting at G7.
gt;gt; Duplicate instances in the same Row should ALL be returned to the same
gt;gt; cell
gt;gt; in Column G on the New Sheet.
gt;gt;
gt;gt; Sample Data Layout:
gt;gt; Columns I J K L M N O P Q R
gt;gt; Row No.76 1 0 1 1 0 1 1 1 0 1
gt;gt; Row No.77 2 2 3 2 1 2 2 0 0 0
gt;gt; Row No.78 3 3 3 3 3 0 3 0 3 0
gt;gt;
gt;gt; Scenario:
gt;gt; Looking for Numeric Criterion 1 (one).
gt;gt;
gt;gt; Expected Results - New Sheet:
gt;gt; Row No.7 Column G (Cell G7) 1111111
gt;gt; Row No.8 Column G (Cell G8) 1
gt;gt;
gt;gt; In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one)
gt;gt; should be
gt;gt; returned to the same cell G7.
gt;gt; In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and
gt;gt; it
gt;gt; should be returned to cell G8.
gt;gt;
gt;gt; Thanks
gt;gt; Sam
I hope it's possible?

Cheers,
Sam

Biff wrote:
gt;I can't wait to see this one!
gt;
gt;Biff
gt;
gt;gt; Is the source data in your dynamic range 'Data' made up of only single
gt;gt; digits?
gt;[quoted text clipped - 41 lines]
gt;gt;gt; Thanks
gt;gt;gt; Sam

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

First, define the following names/references...

Select G7

Insert gt; Name gt; 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))gt;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)lt;=SUM(Array2),SUBSTITUTE(SUMPROD UCT(LARGE(IF(INDEX(Data
,RowIdx,0)=G$5,G$5 1,0),Array1),Array3),G$5 1,G$5) ,quot;quot;)

Hope this helps!

In article lt;5ff6c3193283c@uwegt;, quot;Sam via OfficeKB.comquot; lt;u4102@uwegt;
wrote:

gt; Hi All,
gt;
gt; Find Multiple instances of Numeric Criterion in Row amp; Return To a Single
gt; Column.
gt;
gt; I have a Dynamic Named Range quot;Dataquot; spanning 10 Columns and many Rows.
gt; Each Row may contain duplicates of the Numeric Criterion.
gt;
gt; I would like to find ALL instances of a specific Numeric Criterion across
gt; each single Row in the Dynamic Range quot;Dataquot; and have the Results returned to
gt; a New Sheet in a single column.
gt;
gt; NEW Sheet:
gt; The Numeric Criterion is housed in G5.
gt; The matched criterion should be returned to the New Sheet starting at G7.
gt; Duplicate instances in the same Row should ALL be returned to the same cell
gt; in Column G on the New Sheet.
gt;
gt; Sample Data Layout:
gt; Columns I J K L M N O P Q R
gt; Row No.76 1 0 1 1 0 1 1 1 0 1
gt; Row No.77 2 2 3 2 1 2 2 0 0 0
gt; Row No.78 3 3 3 3 3 0 3 0 3 0
gt;
gt; Scenario:
gt; Looking for Numeric Criterion 1 (one).
gt;
gt; Expected Results - New Sheet:
gt; Row No.7 Column G (Cell G7) 1111111
gt; Row No.8 Column G (Cell G8) 1
gt;
gt; In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
gt; returned to the same cell G7.
gt; In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
gt; should be returned to cell G8.
gt;
gt; Thanks
gt; Sam