Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List of Qualifying Values
I have a list of Data as follows (commas indicate a new column)
Name, Region, Subject Area1, Subject Area 2, Subject Area 3 Under each of the Subject areas is a value (1-4) that indicates this persons ability. Is there a way that I can an automated list for each of the subject areas that shows all individuals that are above a certain level (ie 3-4): Subject Area 2: Name, Region, 4 Name, Region. 4 Name, Region, 3 Vlookup can only return the first individual. Thank you for any help you can give! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List of Qualifying Values
Here's one way to model it up for automated multiple line returns
dynamic to specifiable multiple criteria in another sheet ... Illustrated in this sample: http://freefilehosting.net/download/43j46 AutoList multiple results by criteria in another sheet.xls Construct: Source data assumed in sheet: x, data from row2 down, Name, Region in cols A & B, with subject area headers in C1 across In another sheet: y, Create a DV in D1 to select the subject area, eg: Subject Area 2 Assume the "from-to" number inputs for the subject scores range will be made in F4:F5 Place In A2: =IF($D$1="","",IF(AND(OFFSET(x!A2,,MATCH($D$1,x!$1 :$1,0)-1)=$F$4,OFFSET(x!A2,,MATCH($D$1,x!$1:$1,0)-1)<=$F$5),ROW(),"")) Leave A1 empty. In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to C2 In D2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(OFFSET(x!$A:$ A,,MATCH($A$1,x!$1:$1,0)-1),SMALL($A:$A,ROWS($1:1)))) Select A2:D2, copy down to cover the max expected extent of source data in x, say down to D100? Minimize/hide col A. Cols B to D returns the required results from x (Name-Region-Subject Area), with all lines neatly packed at the top, dynamic to the specified criteria, ie the subject area selected in D1, and the scores range in F4:F5. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "abergman" wrote: I have a list of Data as follows (commas indicate a new column) Name, Region, Subject Area1, Subject Area 2, Subject Area 3 Under each of the Subject areas is a value (1-4) that indicates this persons ability. Is there a way that I can an automated list for each of the subject areas that shows all individuals that are above a certain level (ie 3-4): Subject Area 2: Name, Region, 4 Name, Region. 4 Name, Region, 3 Vlookup can only return the first individual. Thank you for any help you can give! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Characters Qualifying a formula | Excel Discussion (Misc queries) | |||
Qualifying a generated value as unique across worksheets | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
copy qualifying rows to another spreadsheet | Excel Worksheet Functions |