Find Multiple instances of Single Criterion in Row & Return To a Single Col

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(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

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" <[email protected]> wrote in message
news:[email protected]...
> 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,COLUMNS(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(Data)-
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(SUMPRODUCT(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,COLUMNS(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(Data)-
>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(SUMPRODUCT(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,COLUMNS(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(SUMPRODUCT(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" <[email protected]> wrote in message
news:[email protected]...
> 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,COLUMNS(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(Data)-
> 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(SUMPRODUCT(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,COLUMNS(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(SUMPRODUCT(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 <[email protected]>,
"Biff" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>,
> "Biff" <[email protected]> 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>