List of Qualifying Values

  • Thread starter Thread starter abergman
  • Start date Start date
A

abergman

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!
 
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
 
Back
Top