Problems searching a database and reporting all occurrences of a v

[Guest]

I've got a database of test scores:

Name----# MISSED------Score------Questions Missed ---
A-----------0--------------100
B-----------1--------------97-----------1
C-----------6--------------80-----------1---2---3---4---7---9
D-----------2--------------93-----------2---6
E-----------5--------------83-----------1---2---3---4---5
F-----------4--------------87-----------1---3---4---5---7
G-----------6--------------80-----------4---6---7---8---9---10

I'm trying to have all the data from those individuals that scored <90
report on another worksheet. I haven't figured out how to get excel to search
the SCORE column for scores <90 and report all occurrences... I can get it to
report the first score <90, but don't know how to make it report all of them.

Any insight would be greatly appreciated.

Pat Matak

 

[T. Valko]

Using a filter is the easiest way.

Filter on the score column.

Select any column header
Goto Data>Filter>AutoFilter
Then click the drop arrow on the Score column
Select Custom>is less than>90

Copy the filtered data to your other sheet.

Biff

 

[Guest]

If it's to be dynamic in the other sheet based on an input "Cut-Off" Score,
here's a non-array formulas play which delivers ..

A sample construct is available at:
http://www.savefile.com/files/351882
Extract Lines by Cut Off Score to New Sht.xls

Assume source data in sheet: X,
cols A to F, where the key col C = Score,
data from row2 down

In another sheet: Y (say),
Assume the "cut-off" score will be entered in A2, eg: 90
Paste the same col labels in X into C1:H1

Put in B2:
=IF($A$2="","",IF(X!C2="","",IF(X!C2<$A$2,ROW(),"")))
(Leave B1 blank)

In C2:
=IF(ROW(A1)>COUNT($B:$B),"",INDEX(X!A:A,SMALL($B:$B,ROW(A1))))

Copy C2 to H2. Select B2:H2, copy down to cover the max expected extent of
data in X, say down to H100. Hide away cols B. Cols C to H will return the
required results, ie only the lines with scores less than the cut-off score
input in A2, with all lines neatly bunched at the top. Just change the input
in A2 as desired to retrieve other corresponding results from X.

 

[Guest]

Thanks T. Valko and Max for the advice. I think I figured out how to get it
to do what I want...

This is the exact code I used (don't know how to put it in generic terms for
anyone else to understand). I'm an Excel novice, so my method consists of a
lot of trial and error until I get it to do what I want. Now I think my next
step is learning about using Pivot Tables to consolidate data from different
worksheets into one printable page. I'll post a new question if I run into
more problems...

=IF(ROWS(R$6:R6)<=COUNTIF('SQ 1'!$N$5:$N$29,$P$4),INDEX('SQ
1'!$D$5:$D$29,SMALL(IF('SQ 1'!$N$5:$N$29=$P$4,ROW('SQ 1'!$N$5:$N$29)-ROW('SQ
1'!$N$5)+1),ROWS('SQ 1'!AK$5:AK5))),"")

Again, thanks for the help... you definitely got me thinking on the right
track.

 

[T. Valko]

That's my preferred method!

Tip/hint:

ROW('SQ 1'!$N$5:$N$29)-ROW('SQ 1'!$N$5)+1),ROWS('SQ 1'!AK$5:AK5))),"")

You can shorten the overall length and improve the readability of the
formula by NOT including the sheet names in any of the ROW/ROWS functions:

ROW(N$5:N$29)-ROW(N$5)+1),ROWS($1:1))),"")

The ROW(S) functions don't really reference any sheet or even any cells.
They're used as a means of generating either arrays of numbers for use in
the Index function or to increment as the k argument in the Small function.

Biff

 

[Max]

Pat, you're welcome. Do go as preferred.