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

  • Thread starter Sam via OfficeKB.com
  • Start date
S

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
 
B

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
 
S

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

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

Sam via OfficeKB.com said:
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
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
 
D

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!
 
S

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!
[quoted text clipped - 34 lines]
Thanks
Sam
 
D

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!
 
B

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top