Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 18th 06, 02:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Find and Return Numeric Label based on (Numeric Value) Criterion

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


  #2   Report Post  
Old September 18th 06, 09:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 256
Default Find and Return Numeric Label based on (Numeric Value) Criterion

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 <[email protected], "Sam via OfficeKB.com" <[email protected]
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

  #3   Report Post  
Old September 18th 06, 11:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Find and Return Numeric Label based on (Numeric Value) Criterion

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



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
Search Multiple columns for criterion asterisk (*) and Return Numeric Label Sam via OfficeKB.com Excel Worksheet Functions 8 July 14th 06 06:56 PM
Find Multiple instances of Single Criterion in Row & Return To a Single Col Sam via OfficeKB.com Excel Worksheet Functions 16 May 10th 06 03:00 AM
Find Numeric Criterion in Column & Return the Numeric Value from Row above Sam via OfficeKB.com Excel Worksheet Functions 6 April 27th 06 02:50 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Match Single Numeric Criteria and Return Multiple Numeric Labels Sam via OfficeKB.com Excel Worksheet Functions 3 December 30th 05 09:01 PM


All times are GMT +1. The time now is 07:53 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017