Searching results for duplicate fails

[ouamarc]

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

 

[ouamarc]

Would I use a script for this, or is there an excel function that can do this task?

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Subject code	Student No	Grade		Count of Grade	Column Labels
2	a	S1	F		Row Labels	C	D	F	P	F-IN	Grand Total
3	b	S2	F		S1	2		2	1	1	6
4	c	S3	D		a	1		1		1	3
5	b	S1	C		b	1		1			2
6	a	S1	F-IN		c				1		1
7	a	S2	P		S2			1	1	3	5
8	c	S2	F-IN		a				1		1
9	c	S1	P		b			1			1
10	c	S2	F-IN		c					3	3
11	c	S3	F		S3		1	1			2
12	b	S1	F		c		1	1			2
13	c	S2	F-IN		Grand Total	2	1	4	2	4	13
14	a	S1	C
15
Fail

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

Excel Workbook
	E	F	G	H
1	Count of Grade	Column Labels
2	Row Labels	F	F-IN	Grand Total
3	S1	2	1	3
4	a	1	1	2
5	b	1		1
6	S2	1	3	4
7	b	1		1
8	c		3	3
9	S3	1		1
10	c	1		1
11	Grand Total	4	4	8
Fail

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

 

[ouamarc]

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]

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
	A	B	C
1	Course	ID	Grade
2	Economics 100	ID20001	F
3	Management 100	ID20001	C
4	Accounting 100	ID201484	D
5	Economics 100	ID20001	F
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND($C2="F",COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2,$C$1:$C1,"F"))	Abc