S
Sam via OfficeKB.com
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
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