Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reporting with Database function | Excel Discussion (Misc queries) | |||
Links to External Database | Excel Discussion (Misc queries) | |||
Excel Worksheet Form / Database reporting | Excel Discussion (Misc queries) |