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

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

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 "Data", is there another solution
or workaround possible.

Further assistance much appreciated.

Cheers,
Sam

Biff wrote:
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

Hi All,

[quoted text clipped - 38 lines]
Thanks
Sam

--
Message posted via http://www.officekb.com

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

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

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

http://xcell05.free.fr/english/

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:5ff86677b108c@uwe...
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 "Data", is there another
solution
or workaround possible.

Further assistance much appreciated.

Cheers,
Sam

Biff wrote:
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(Dat a)-76+1),ROWS($1:1)),11)

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

Biff

Hi All,

[quoted text clipped - 38 lines]
Thanks
Sam

--
Message posted via http://www.officekb.com

Hi Domenic,

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:
Is the source data in your dynamic range 'Data' made up of only single
digits?

Hi All,

[quoted text clipped - 34 lines]
Thanks
Sam

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

Hi Biff,

Thank you for assitance.

Cheers,
Sam

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

http://xcell05.free.fr/english/

Biff

Hi Biff,

[quoted text clipped - 41 lines]
Thanks
Sam

--
Message posted via http://www.officekb.com

I can't wait to see this one!

Biff

"Domenic" wrote in message
...
Is the source data in your dynamic range 'Data' made up of only single
digits?

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

I hope it's possible?

Cheers,
Sam

Biff wrote:
I can't wait to see this one!

Biff

Is the source data in your dynamic range 'Data' made up of only single
digits?
[quoted text clipped - 41 lines]
Thanks
Sam

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

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

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

In that case, we'll only need the following references for the defined
names...

Select G7

Insert Name Define

Array1:

=ROW(INDEX(Sheet2!$A:$A,1):INDEX(Sheet2!$A:$A,COLU MNS(Data)))

Array2:

=INDEX(Data,ROWS(Sheet2!$G$7:$G7),0)

Array3:

=10^(Array1*LEN(Sheet2!G$5+1))/10^LEN(Sheet2!G$5+1)

Then, use the following formula instead...

G7, copied down and across:

=IF(ISNUMBER(MATCH(G$5,Array2,0)),SUBSTITUTE(SUMPR ODUCT(LARGE(IF(Array2=G
$5,G$5+1,0),Array1),Array3),G$5+1,G$5),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <60013fc07d3b4@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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

Sam says: Absolutely Brilliant!!

I say: Did you expect anything less?

Wow! You don't mind if I put this in my stash, do you?

Biff

"Domenic" wrote in message
...
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

Hi Domenic,

Superb! And as Biff said, WOW!

Thank you for your time and all your help.

Cheers,
Sam

Domenic wrote:
In that case, we'll only need the following references for the defined
names...

Select G7
Insert Name Define
Array1:
=ROW(INDEX(Sheet2!$A:$A,1):INDEX(Sheet2!$A:$A,COL UMNS(Data)))

Array2:
=INDEX(Data,ROWS(Sheet2!$G$7:$G7),0)

Array3:
=10^(Array1*LEN(Sheet2!G$5+1))/10^LEN(Sheet2!G$5+1)

Then, use the following formula instead...
G7, copied down and across:
=IF(ISNUMBER(MATCH(G$5,Array2,0)),SUBSTITUTE(SUMP RODUCT(LARGE(IF(Array2=G
$5,G$5+1,0),Array1),Array3),G$5+1,G$5),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

[quoted text clipped - 20 lines]
Cheers,
Sam

--
Message posted via http://www.officekb.com

Biff wrote:
Sam says: Absolutely Brilliant!!
Biff says: Did you expect anything less?

No Way!

Cheers,
Sam

--
Message posted via http://www.officekb.com

In article ,
"Biff" wrote:

You don't mind if I put this in my stash, do you?

By all means... I should probably put this in my stash as well... <VBG

Brilliant!

--
Peo

"Domenic" wrote in message
...
In article ,
"Biff" wrote:

You don't mind if I put this in my stash, do you?

By all means... I should probably put this in my stash as well... <VBG