|
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Hi All,
I am using a Dynamic Named Range "Data", spans 55 Columns and many Rows. I need a Formula to separately Index each Column of the Dynamic Range "Data". The Numeric Criterion will vary. The Data is in Columnar Format. In the Sample Data single digits are preceded with a zero for alignment with double digits. I would like to find multiple instances of a Numeric Criterion in a specific Column, and have both the Criterion and the Numeric Value that is located in the Row above the Numeric Criterion Returned to a New Sheet in separate cells across a Row. Scenario: 1) Find In Column "E", the first Column of the Dynamic Range "Data", all instances of the Numeric Value "5" (five). Then for each instance of Criterion "5" Return To a New Sheet across a Row in separate cells the Criterion "5" with the Numeric Value located in the Row above the Numeric Criterion. 2) Find in Column "F" the Numeric Criterion "4" and Return each instance of Criterion "4" with the Numeric Value located above Criterion "4". Sample Data & Layout: RowNo. Col"E" Col"F" etc Row50 30 04 Row49 64 64 Row48 27 04 Row47 05 05 Row46 20 20 Row45 80 72 Row44 88 04 Row43 05 08 Row42 50 27 Row41 42 39 Row40 30 04 Row39 05 04 Row38 05 50 Row37 59 19 Expected Results - New Sheet: 1) Criterion "5" 5 5 5 30 5 88 5 27 Looking at the above Sample Layout, the Expected Results for Criterion "5" are from Row38 & Row39, Row39 & 40, Row43 & Row44, Row47 & Row48. 2) Criterion "4" 4 4 4 39 4 72 4 64 Looking at the above Sample Layout, the Expected Results for Criterion "4" are from Row39 & Row40, Row40 & Row41, Row44& Row45, Row48 & Row49 Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Some clarification...
With the data laid out as you've described, do you mean the 'numeric value that's located in the row below'? Or should the row numbers for your data be in reverse order? In article <5f5dd7bf57f0c@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I am using a Dynamic Named Range "Data", spans 55 Columns and many Rows. I need a Formula to separately Index each Column of the Dynamic Range "Data". The Numeric Criterion will vary. The Data is in Columnar Format. In the Sample Data single digits are preceded with a zero for alignment with double digits. I would like to find multiple instances of a Numeric Criterion in a specific Column, and have both the Criterion and the Numeric Value that is located in the Row above the Numeric Criterion Returned to a New Sheet in separate cells across a Row. Scenario: 1) Find In Column "E", the first Column of the Dynamic Range "Data", all instances of the Numeric Value "5" (five). Then for each instance of Criterion "5" Return To a New Sheet across a Row in separate cells the Criterion "5" with the Numeric Value located in the Row above the Numeric Criterion. 2) Find in Column "F" the Numeric Criterion "4" and Return each instance of Criterion "4" with the Numeric Value located above Criterion "4". Sample Data & Layout: RowNo. Col"E" Col"F" etc Row50 30 04 Row49 64 64 Row48 27 04 Row47 05 05 Row46 20 20 Row45 80 72 Row44 88 04 Row43 05 08 Row42 50 27 Row41 42 39 Row40 30 04 Row39 05 04 Row38 05 50 Row37 59 19 Expected Results - New Sheet: 1) Criterion "5" 5 5 5 30 5 88 5 27 Looking at the above Sample Layout, the Expected Results for Criterion "5" are from Row38 & Row39, Row39 & 40, Row43 & Row44, Row47 & Row48. 2) Criterion "4" 4 4 4 39 4 72 4 64 Looking at the above Sample Layout, the Expected Results for Criterion "4" are from Row39 & Row40, Row40 & Row41, Row44& Row45, Row48 & Row49 Thanks Sam |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Hi Domenic,
Apologies for not responding sooner and my incorrect Sample Layout. Some clarification... With the data laid out as you've described, do you mean the 'numeric value that's located in the row below'? Yes, the Row below Or should the row numbers for your data be in reverse order? Sorry, No. I should have said the search for the Numeric Criterion will start from the bottom of the Dynamic Range up - so, Row1000 is the bottom /start and Row1 is the top /end. If there is a Numeric Criterion at the top (Row1) of the Dynamic Range (and no cell above) still Return the Numeric Criterion but with "Empty Text" for the missing Numeric Value. Correction: - Sample Data & Layout: RowNo. Col"E" Col"F" etc Row37 30 04 Row38 64 64 Row39 27 04 Row40 05 05 Row41 20 20 Row42 80 72 Row43 88 04 Row44 05 08 Row45 50 27 Row46 42 39 Row47 30 04 Row48 05 04 Row49 05 50 Row50 59 19 Cheers, Sam Domenic wrote: Some clarification... With the data laid out as you've described, do you mean the 'numeric value that's located in the row below'? Or should the row numbers for your data be in reverse order? Hi All, [quoted text clipped - 54 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Assumptions:
Sheet2 will contain the results Column B, starting at B2, will contain the criteria Each row will index a different column from the dynamic range named 'Data'. For example, Row 2 will index Column E, Row 3 will index Column F, etc. Defined Name: Select Sheet2!C2 Insert Name Define Name: DataIdx Refers to: =INDEX(Data,0,ROWS(Sheet2!C$2:C2)) Click Ok Formulas: C2, copied down: =SUMPRODUCT(--(DataIdx=B2),--(ROW(DataIdx)-MIN(ROW(DataIdx))+11)) D2, copied across and down: =IF(COLUMNS($D2:D2)<=$C2*2,IF(MOD(COLUMN()-COLUMN($D2),2)=0,$B2,INDEX(Dat aIdx,LARGE(IF(DataIdx=$B2,ROW(DataIdx)-MIN(ROW(DataIdx))+1),INT((COLUMN() -COLUMN($D2))/2)+1)-1)),"") ....confirmed with CONTROL+SHIFT+ENTER. Note: Here is one occasion where it would be easier to have the criterion and numeric value returned in the same cell instead of separately. Post back if you'd prefer it this way. Hope this helps! In article <5f6075872f735@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Apologies for not responding sooner and my incorrect Sample Layout. Some clarification... With the data laid out as you've described, do you mean the 'numeric value that's located in the row below'? Yes, the Row below Or should the row numbers for your data be in reverse order? Sorry, No. I should have said the search for the Numeric Criterion will start from the bottom of the Dynamic Range up - so, Row1000 is the bottom /start and Row1 is the top /end. If there is a Numeric Criterion at the top (Row1) of the Dynamic Range (and no cell above) still Return the Numeric Criterion but with "Empty Text" for the missing Numeric Value. Correction: - Sample Data & Layout: RowNo. Col"E" Col"F" etc Row37 30 04 Row38 64 64 Row39 27 04 Row40 05 05 Row41 20 20 Row42 80 72 Row43 88 04 Row44 05 08 Row45 50 27 Row46 42 39 Row47 30 04 Row48 05 04 Row49 05 50 Row50 59 19 Cheers, Sam Domenic wrote: Some clarification... With the data laid out as you've described, do you mean the 'numeric value that's located in the row below'? Or should the row numbers for your data be in reverse order? Hi All, [quoted text clipped - 54 lines] Thanks Sam |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Hi Domenic,
Thank you very much for providing a solution. I thought it may complicate things by returning both the criterion and numeric value to the same cell. Here is one occasion where it would be easier to have the criterion and numeric value returned in the same cell instead of separately. Post back if you'd prefer it this way. Yes, it woud be great to have the criterion and numeric value returned in the same cell. Cheers, Sam Domenic wrote: Assumptions: Sheet2 will contain the results Column B, starting at B2, will contain the criteria Each row will index a different column from the dynamic range named 'Data'. For example, Row 2 will index Column E, Row 3 will index Column F, etc. Defined Name: Select Sheet2!C2 Insert Name Define Name: DataIdx Refers to: =INDEX(Data,0,ROWS(Sheet2!C$2:C2)) Click Ok Formulas: C2, copied down: =SUMPRODUCT(--(DataIdx=B2),--(ROW(DataIdx)-MIN(ROW(DataIdx))+11)) D2, copied across and down: =IF(COLUMNS($D2:D2)<=$C2*2,IF(MOD(COLUMN()-COLUMN($D2),2)=0,$B2,INDEX(Dat aIdx,LARGE(IF(DataIdx=$B2,ROW(DataIdx)-MIN(ROW(DataIdx))+1),INT((COLUMN() -COLUMN($D2))/2)+1)-1)),"") ...confirmed with CONTROL+SHIFT+ENTER. Note: Here is one occasion where it would be easier to have the criterion and numeric value returned in the same cell instead of separately. Post back if you'd prefer it this way. Hope this helps! Hi Domenic, [quoted text clipped - 48 lines] Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200604/1 |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
In article <5f67088fe7dd1@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote: Yes, it woud be great to have the criterion and numeric value returned in the same cell. In that case, try the following formula instead... D2, copied across and down: =IF(COLUMNS($D2:D2)<=$C2,$B2&" "&INDEX(DataIdx,LARGE(IF(DataIdx=$B2,ROW(DataI dx)-MIN(ROW(DataIdx))+1),CO LUMNS($D2:D2))-1),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! |
Find Numeric Criterion in Column & Return the Numeric Value from Row above
Hi Domenic,
Brilliant! Thank you for all your help. Cheers, Sam Domenic wrote: Yes, it woud be great to have the criterion and numeric value returned in the same cell. In that case, try the following formula instead... D2, copied across and down: =IF(COLUMNS($D2:D2)<=$C2,$B2&" "&INDEX(DataIdx,LARGE(IF(DataIdx=$B2,ROW(DataId x)-MIN(ROW(DataIdx))+1),CO LUMNS($D2:D2))-1),"") ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! -- Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 05:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com