Excel data representation: show me all people who did not pass the exam

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.