Hi All,

My Numeric Data spans 61 columns and many rows. The Data comprises Numeric
Values and Numeric Labels. The Numeric Labels and Numeric Values are in 2
separate consecutive rows of data, a blank row and then a new set of Numeric
Labels and Values in 2 separate consecutive rows, a blank row etc.

Can you provide a Formula that can return all the relevant Numeric Labels
that are paired with a specific Numeric Value located in the row below.

I would like the Numeric Labels returned in ascending order - with
duplicates; down a single column.

The Numeric Labels are 1-61
The Numeric Values are 0-1000.

Sample Layout:
Row F117:BO117 Numeric Labels (1-61)
Row F118:BO118 Numeric Values(0-200) in descending order
Row F119:BO119 Blank/ Empty Row
Row F120:BO120 Numeric Labels (1-61)
Row F121:BO121 Numeric Values(0-200) in descending order
Row F122:BO122 Blank/ Empty Row
Row F123:BO123 Numeric Labels (1-61)
Row F124:BO124 Numeric Values(0-200) in descending order
Row F125:BO125 Blank/ Empty Row
Row F126:BO126 Numeric Labels (1-61)
Row F127:BO127 Numeric Values(0-200) in descending order
Row F128:BO128 Blank/ Empty Row
Row F129:BO129 Numeric Labels (1-61)
Row F130:BO130 Numeric Values(0-200) in descending order
Row F131:BO131 Blank/ Empty Row
Row etc
Row etc
Row F225:BO225 Numeric Labels (1-61)
Row F226:BO226 Numeric Values(0-200) in descending order
Row F227:BO227 Blank/ Empty Row

Sample Data:
Labels 31 38 3 7 23 26 15 27 45
Values 22 21 19 19 18 18 17 17 17

Labels 23 21 28 44 45 17 61 47 50
Values 61 47 50 35 19 18 18 18 17

Labels 43 1 22 29 14 23 44 9 11
Values 50 19 19 18 18 17 17 16 16

Labels 60 61 43 6 7 12 8 16 44
Values 21 20 20 19 18 18 17 17 17

Labels 38 8 42 43 19 61 6 24 25
Values 50 50 39 19 17 16 16 16 16

Labels 19 45 20 4 8 18 21 34 38
Values 61 60 55 50 50 50 18 18 18

Required Solution:
To find the all Numeric Labels with a Numeric Value of 50 - search Rows
with Numeric
Values; F118:BO118, F121:BO121, F124:BO124, F127:BO127, F130:BO130, etc; etc;
last Row F226:BO226.
Each time 50 appears return the Numeric Label directly above it (down a
single column).

Expected Results:
Using the Sample Data these Numeric Labels should be returned as the Numeric
Value 50 appears direclty below them:
28, 43, 38, 8, 4, 8, 18

Then returned in ascending order (down a single column):
4
8
8
18
28
38
43

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Based on your sample data, try the following...

P117:

=SUM(IF(MOD(ROW(F118:N133)-ROW(F118),3)=0,IF(F118:N133=50,1)))

....confirmed with CONTROL+SHIFT+ENTER

Q117, copied down:

=IF(ROWS(Q$117:Q117)<=P$117,SMALL(IF(MOD(ROW(F$118 :N$133)-ROW(F$118),3)=0
,IF(F$118:N$133=50,F$117:N$132)),ROWS(Q$117:Q117)) ,"")

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

Hope this helps!

In article <6679e68dc447d@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

My Numeric Data spans 61 columns and many rows. The Data comprises Numeric
Values and Numeric Labels. The Numeric Labels and Numeric Values are in 2
separate consecutive rows of data, a blank row and then a new set of Numeric
Labels and Values in 2 separate consecutive rows, a blank row etc.

Can you provide a Formula that can return all the relevant Numeric Labels
that are paired with a specific Numeric Value located in the row below.

I would like the Numeric Labels returned in ascending order - with
duplicates; down a single column.

The Numeric Labels are 1-61
The Numeric Values are 0-1000.

Sample Layout:
Row F117:BO117 Numeric Labels (1-61)
Row F118:BO118 Numeric Values(0-200) in descending order
Row F119:BO119 Blank/ Empty Row
Row F120:BO120 Numeric Labels (1-61)
Row F121:BO121 Numeric Values(0-200) in descending order
Row F122:BO122 Blank/ Empty Row
Row F123:BO123 Numeric Labels (1-61)
Row F124:BO124 Numeric Values(0-200) in descending order
Row F125:BO125 Blank/ Empty Row
Row F126:BO126 Numeric Labels (1-61)
Row F127:BO127 Numeric Values(0-200) in descending order
Row F128:BO128 Blank/ Empty Row
Row F129:BO129 Numeric Labels (1-61)
Row F130:BO130 Numeric Values(0-200) in descending order
Row F131:BO131 Blank/ Empty Row
Row etc
Row etc
Row F225:BO225 Numeric Labels (1-61)
Row F226:BO226 Numeric Values(0-200) in descending order
Row F227:BO227 Blank/ Empty Row

Sample Data:
Labels 31 38 3 7 23 26 15 27 45
Values 22 21 19 19 18 18 17 17 17

Labels 23 21 28 44 45 17 61 47 50
Values 61 47 50 35 19 18 18 18 17

Labels 43 1 22 29 14 23 44 9 11
Values 50 19 19 18 18 17 17 16 16

Labels 60 61 43 6 7 12 8 16 44
Values 21 20 20 19 18 18 17 17 17

Labels 38 8 42 43 19 61 6 24 25
Values 50 50 39 19 17 16 16 16 16

Labels 19 45 20 4 8 18 21 34 38
Values 61 60 55 50 50 50 18 18 18

Required Solution:
To find the all Numeric Labels with a Numeric Value of 50 - search Rows
with Numeric
Values; F118:BO118, F121:BO121, F124:BO124, F127:BO127, F130:BO130, etc; etc;
last Row F226:BO226.
Each time 50 appears return the Numeric Label directly above it (down a
single column).

Expected Results:
Using the Sample Data these Numeric Labels should be returned as the Numeric
Value 50 appears direclty below them:
28, 43, 38, 8, 4, 8, 18

Then returned in ascending order (down a single column):
4
8
8
18
28
38
43

Cheers,
Sam

Hi Domenic,

Brilliant! Thank you very much.

Cheers,
Sam

Domenic wrote:
Based on your sample data, try the following...

P117:

=SUM(IF(MOD(ROW(F118:N133)-ROW(F118),3)=0,IF(F118:N133=50,1)))

...confirmed with CONTROL+SHIFT+ENTER

Q117, copied down:

=IF(ROWS(Q$117:Q117)<=P$117,SMALL(IF(MOD(ROW(F$11 8:N$133)-ROW(F$118),3)=0
,IF(F$118:N$133=50,F$117:N$132)),ROWS(Q$117:Q117) ),"")

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

Hope this helps!

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