Finding Duplicates Within Nested Data

[ChrisQs]

Hello,

Could someone please let me know if it is possible to find duplicates within nested data in Excel.

Here is the situation I am dealing with:

- Students come to a school 9 times (Column A)

- Each time they should take a different course (Column B - course a to h)

- Column C is the course that the student has completed.

I want to create a way to automatically identify duplicates within the nested data. For example, Jack has completed course a twice.

But, I don't want to identify Jill as being a duplicate, as shown below.

Dealing with 1,000 students in one excel sheet, so looking for any way to automatically find duplicates.
Any advice would be greatly appreciated.

Christopher

 

[dave3009]

Use a formula

=COUNTIFS($A:$A,$A2,$C:$C,$C2)>1

test2.xlsb
	A	B	C
1	Name	Course1	Course2
2	Jack	a	a
3	Jack	b	b
4	Jack	c	c
5	Jack	d	d
6	Jack	e	e
7	Jack	f	f
8	Jack	g	g
9	Jack	h	a
10	Jill	a	a
11	Jill	b	b
12	Jill	c	c
13	Jill	d	d
14	Jill	e	e
15	Jill	f	f
16	Jill	g	g
17	Jill	h	h
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C17	Expression	=COUNTIFS($A:$A,$A2,$C:$C,$C2)>1	text	NO