extracting data by a criteria ? IF this , then That?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

AS a teacher I have two columns - 1 with childrens names the 2nd cloumn has
scores.

I want to be able to extract those children by name who have scored within a
range of marks or < a mark or scored a set mark etc and have this shown in a
report or area of the spreadsheet.

Possible?

ALSO can those children who meet the criteria have the original cells with
their 2 columns automatically change colour ? as a way of highlighting them?

Thanks
for your assistance
Mike
 
Mike,

Assuming the names are in A, and the scores in B, then select a number of
cells in another column, and then in the formula bar, enter this formula

=IF(ISERROR(SMALL(IF(($B$1:$B$20>20)*($B$1:$B$20<=40),ROW($A1:$A20),""),ROW(
$A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF(($B$1:$B$20>20)*($B$1:$B$20<=40),ROW($A1:$A20),"")
,ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

This will test for all students with a score of 21-40.

You can highlight the originals using conditional formatting using a formula
of

=COUNTIF(E:E,$A1)>0

assuming that you put the formula in columen E


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Use Data/Autofilter/Custom!
E.g.
scored within a range of marks: >= lower limit
and
<= upper limit
etc.
ALSO can those children who meet the criteria have the original cells with
their 2 columns automatically change colour ? as a way of highlighting them?
Click to expand...

Use Formatting/Conditional formatting:
If criterium is Score >=3 then Cell value >=3 and choose a color!

Regards,
Stefi
 
Thanks Stefi- very good.

I have managed to work out the basics of Auto Filter and Custom filter
within this Auto Filter


Coluumn A has say six childrens names and Col B has scores 4,5,3,6,7,6. I
have been able to identify those children with say <+5 OK but struggling with
ADvanced Filters where in I want to have these filtered children that match
the criteria copied onto another area of the Sheet say E1. I cannot work this
out - can you explain a little clearer than the Excel Help?

Also I cannot seem to get the conditional formatting to bring the original
columns to a say Blue colour if thaey make the <+ criteria.

More assistance?
Thanks
Mike
 
Hi Mike,

copying onto another area of the Sheet say E1:
Your original table:
A B
1 name score
2 name1 4
3 name2 5
4 name3 3
5 name4 6
6 name5 7
7 name6 6

Above your original table place these new rows getting the following table:
1 name score
2 <5
3 name score
4 name1 4
5 name2 5
6 name3 3
7 name4 6
8 name5 7
9 name6 6

Fill in the Custom filter dialog box as follows:
Check Option button Copy to another place
List range: $A$4:$B$10 (place the cursor in the field and select these
cells)
Filter range: $B$1:$B$2 -"-
Copy to: $E$1 -"-
Click OK

conditional formatting to bring the original columns to a say Blue colour if
they make the <+5 criteria.

Select range A5:B9
Bring up Conditional formatting

Select Formula (on the left side of the box)
Type =$B5<5 as the formula
Select a color
Click OK

Regards,
Stefi
 
Back
Top