|
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
Biff wrote:
Sam says: Absolutely Brilliant!! Biff says: Did you expect anything less? No Way! Cheers, Sam -- Message posted via http://www.officekb.com |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
Find Multiple instances of Single Criterion in Row & Return To a Single Col
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 |
| All times are GMT +1. The time now is 01:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com