Identifying Duplicate Values in a Dynamic Range

[Mike Collins]

Hi all,

Thought this problem was relatively simple but I'm struggling to find a solution (possibly not enough coffee yet).

I have a sheet which contains Student data (ID, Name etc), the subjects they want to study, and a block letter where that subject can be studied:

{A} {B} {C} {D}
Student Subject Block Conflict?
Student1 Chemistry U
Student1 Physics V
Student1 Maths W
Student1 Law X

Student2 English Y
Student2 Maths W
Student2 Law X

The number of subjects a student can take is variable. What I want to do is identify conflicts in the block letters (Column C) for a given student. So in the above example, everything is fine but:

Student 1 Chemistry U <-Duplicate
Student 1 Physics V
Student 1 Maths U <-Duplicate
Student 1 Law X

Ideally I'm looking to construct a formula in column D to identify this. I'm not really bothered how I flag the duplication / conflict of Block letter i.e. I can simply have a "Y" letter in D if there is an issue and then identify this with conditional formatting. The thing that is causing me the main problem is the dynamic range of the students choices.

Any help would be greatly appreciated.

Many thanks in advance,

Mike

 

[Faseeh]

HI Mike,

Simply join (concatenate) first and third column of your data using ampersand "&" and use conditional formatting to highlight duplicates.

 

[Mike Collins]

Ahhh,

Sometimes the most simply ideas are the best. Ive been messing around with array formulas all morning. Your suggestion is simple, elegant and spot on.

Thanks.