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

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

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

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

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

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

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(DataIdx)-MIN(ROW(DataIdx))+1),CO
LUMNS($D2:D2))-1),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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(DataIdx)-MIN(ROW(DataIdx))+1),CO
>LUMNS($D2:D2))-1),"")

>...confirmed with CONTROL+SHIFT+ENTER.

>Hope this helps!

--
Message posted via http://www.officekb.com