Problems searching a database and reporting all occurrences of a v
 

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

Problems searching a database and reporting all occurrences of a v
 

Using a filter is the easiest way.

Filter on the score column.

Select any column header
Goto DataFilterAutoFilter
Then click the drop arrow on the Score column
Select Customis less than90

Copy the filtered data to your other sheet.

Biff

"Pat" wrote in message
...
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

Problems searching a database and reporting all occurrences of a v
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pat" wrote:
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

Problems searching a database and reporting all occurrences of
 

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.

--
Pat Matak




"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pat" wrote:
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

Problems searching a database and reporting all occurrences of
 

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

"Pat" wrote in message
...
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.

--
Pat Matak




"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pat" wrote:
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

Problems searching a database and reporting all occurrences of
 

Pat, you're welcome. Do go as preferred.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pat" wrote in message
...
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.

--
Pat Matak