Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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!
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
sam518 via OfficeKB.com
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format as a MACRO Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
AVERAGE Row of Numbers and Return Corresponding Numeric Label Sam via OfficeKB.com Excel Worksheet Functions 14 September 20th 05 01:07 AM
Find a empty cell in next column Michael Excel Discussion (Misc queries) 3 June 15th 05 02:18 PM


All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"