Searching results for duplicate fails

ouamarc

New Member
Joined
Mar 8, 2011
Messages
3
Hi,

As my first post, I would like to say that so far this forum has been a tremendous help for me in figuring out some tricky business regarding Excel, thank you.

Now, to outline a problem I am facing now, along with how I currently do it...

I am sent a record of about 25,000 students which includes details such as their student number, the subject they have studied, and the grade they achieved for that subject.

I am then asked to find all the students who have failed the same unit twice, as this requires further action from school administration.

Now, at the moment I use a function like this:

=IF(AND(F3=F4,A3=A4, OR(G3="F", G3="F-IN"), OR(G4="F", G4="F-IN")),"Failed the same unit twice","")

Admittedly, it is quite clumsy. For it to work, prior to typing in the function I must order the whole spreadsheet first by student number (column F), and then by their subject code (column A).

It may be clear to you that in doing that, I will now have each student and the units they study in sequential order, so if they have done the same unit twice, their results (column G) can be compared to see if there are fails or not. You may note in my above code a student can fail in two ways, F or F-IN.

In the cell I have that formula in, it says 'Failed the same unit twice' if they have failed twice, otherwise it stays blank.

I can then Filter the results if I want, only displaying the 'Failed the same unit twice' students which I can then copy and paste into a new sheet to give to administration.

Can anyone think of a more efficient way of completing this? It would be great if there was a code with 3 variables declared right at the top, 'U', 'S', and 'G' (unit, student, grade) so I could change them to suite the particular layout of the spreadsheet I am given (for example if column X was the student number I could define the variable 'S' as 'X'...?).

One of the main challenges is that I cannot think of ways to complete the task without manually sorting the results, as explained above. Perhaps a code/macro can do that for me?

I'd love some direction/help/suggestions for this task,

ouamarc
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ouamarc

New Member
Joined
Mar 8, 2011
Messages
3
Would I use a script for this, or is there an excel function that can do this task?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,690
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I've used a simplified layout to see if this might be any use. This is what I have done, using Excel 2010.

Create a Pivot Table with Student No and Subject Code as Row labels, Grade as Column Label and Grade in the data area. This is the result:

Excel Workbook
ABCDEFGHIJKL
1Subject codeStudent NoGradeCount of GradeColumn Labels
2aS1FRow LabelsCDFPF-INGrand Total
3bS2FS122116
4cS3Da1113
5bS1Cb112
6aS1F-INc11
7aS2PS21135
8cS2F-INa11
9cS1Pb11
10cS2F-INc33
11cS3FS3112
12bS1Fc112
13cS2F-INGrand Total2142413
14aS1C
15
Fail




Then I used the drop-down next to Column Labels to filter for just the Grades 'F' and 'F-IN':

Excel Workbook
EFGH
1Count of GradeColumn Labels
2Row LabelsFF-INGrand Total
3S1213
4a112
5b11
6S2134
7b11
8c33
9S311
10c11
11Grand Total448
Fail




The Grand Total column shows us that student S1 failed subject 'a' twice and student S2 failed subject 'c' three times.
 

ouamarc

New Member
Joined
Mar 8, 2011
Messages
3
Thank you Peter,

Can you also suggest a way to highlight the second occurance of the fail in the main spreadsheet?

e.g.

Economics 100 - ID20001 - F
Management 100 - ID20001 - C
Accounting 100 - ID201484 - D
Economics 100 - ID20001 - F
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,690
Office Version
  1. 365
Platform
  1. Windows
You can use Conditional Formatting (CF). However, CF is fairly resource-hungry so if this is to be used on your sheet with 25,000 rows you may possibly find the sheet slow to respond.

For a layout like below, select A2:Cxx and apply the CF shown. I have assumed Excel 2007+. If you have an earlier version then replace the CF formula with this one:

=AND($C2="F",SUMPRODUCT(--($A$1:$A1=$A2),--($B$1:$B1=$B2),--($C$1:$C1="F")))

If you need further help with how to implement or adapt this then post back with more details, including what Excel version you are using.

Excel Workbook
ABC
1CourseIDGrade
2Economics 100ID20001F
3Management 100ID20001C
4Accounting 100ID201484D
5Economics 100ID20001F
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =AND($C2="F",COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2,$C$1:$C1,"F"))Abc
 

Forum statistics

Threads
1,141,842
Messages
5,708,916
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top