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

[Sam via OfficeKB.com]

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

 

[Biff]

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]

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

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

 

[Domenic]

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

 

[Biff]

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,

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

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

 

[Sam via 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

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

Hi All,

[quoted text clipped - 34 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you for assitance.

Cheers,
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

Hi Biff,

[quoted text clipped - 41 lines]

 

[Biff]

I can't wait to see this one!

Biff

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

 

[Sam via OfficeKB.com]

I hope it's possible?

Cheers,
Sam

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]

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

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

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

 

[Domenic]

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!

 

[Biff]

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

 

[Sam via OfficeKB.com]

Hi Domenic,

Superb! And as Biff said, WOW!

Thank you for your time and all your help.

Cheers,
Sam

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


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

 

[Sam via OfficeKB.com]

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

No Way!

Cheers,
Sam

 

[Domenic]

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>

 

[Peo Sjoblom]

Brilliant!

--
Peo

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