Find Numeric Criterion in Column & Return the Numeric Value from Row above

[Sam via OfficeKB.com]

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

 

[Domenic]

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?

 

[Sam via OfficeKB.com]

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

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

 

[Domenic]

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($D22)<=$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,

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

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

 

[Sam via OfficeKB.com]

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

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($D22)<=$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]

 

[Domenic]

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($D22)<=$C2,$B2&"
"&INDEX(DataIdx,LARGE(IF(DataIdx=$B2,ROW(DataIdx)-MIN(ROW(DataIdx))+1),CO
LUMNS($D22))-1),"")

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

Hope this helps!

 

[sam518 via OfficeKB.com]

Hi Domenic,

Brilliant! Thank you for all your help.

Cheers,
Sam

In that case, try the following formula instead...