Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Background

I have an excel spreadsheet with the results of a pass/no-pass exam. Students are allowed to take the exam as often as they want until they either pass, or give up trying.

student         ;; result   ;; date
sally@fiz.edu   ;; no-pass  ;; 2000-06-07 
bravo@fiz.edu   ;; pass     ;; 2000-06-07 
charlie@fiz.edu ;; pass     ;; 2000-06-07 
delta@fiz.edu   ;; no-pass  ;; 2000-06-07
alpha@fiz.edu   ;; pass     ;; 2000-06-07 
sally@fiz.edu   ;; pass     ;; 2000-06-08
delta@fiz.edu   ;; no-pass  ;; 2000-06-08

Question

Using a pivot-table or something else, how can I get excel to show me a clean report or representation of this data on another sheet that answers the question:

Who are all the people who took the exam, but never got a passing grade?

In the above example it would just show me delta@fiz.edu ;; no-pass ;; with all the dates that delta took the exam.

I know excel is not a database nor a reporting tool per-se, but it would be great if I could get it to do this.

share|improve this question
    
this might be better suited for stack overflow –  Daisetsu Jun 7 '10 at 18:02

3 Answers 3

I put the data in you question into Columns A,B and C in a sheet and in cell D2 entered the array formula:

=SUM(($A$2:$A$8=A2)*($B$2:$B$8="pass"))

and then copied it down the column (rememer to use ctrl+shift+enter to enter the formula).

Column D then gives you a 0 where the student has not passed the exam.

You can the use "remove duplicates" on the data tab to remove the duplicates (clear the date box in the dialog) to leave you with a list of people who have not passed the exam, and then filter (again on the "data" tab) by column D.

share|improve this answer
    
If you want to do a regular formula instead of an array formula, you could do =SUMProduct(--($A$2:$A$8=A2),--($B$2:$B$8="pass")) –  Jarvin Jun 7 '10 at 20:35
    
+1 nice avoidance of pivot table. Often i find people have trouble with array formulas though. –  DaveParillo Jun 11 '10 at 4:59

record a macro... do the following

select entire table
create new worksheet
paste table
sort on result

then if you really want only the results, you'll need some conditional logic in the VBA file that represents your macro


otherwise learn how to program in VSTO and do it in .NET, this is a nicer solution, but possibly more work depending on your abilities.

share|improve this answer

I would add a 4th column to turn the 'pass' 'fail' into a number, example, =If(B2="pass",1,0). Let's call this new column "test". Then extend this formula for all your rows.

Then it is easy to make a pivot table that is setup with students as a row field, dates as column fields and test as your data field. It doesn't much matter if the test data is 'Sum of test" or "Count of test"

Then hide all the test values > 0.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.