Code To Find Duplicates And Highlight

[Dazzawm]

Is there a code or formula for when there is matching cells in column C that will then look in column AE and highlight any duplicate values please?

 

[Legacy 143009]

Select Column C. Navigate to Conditional Formatting. Click New Rule. Select New Rule with Formula. Paste the formula below:

=COUNTIF($AE:$AE,C1)>0

Define formatting.

 

[Dazzawm]

Select Column C. Navigate to Conditional Formatting. Click New Rule. Select New Rule with Formula. Paste the formula below:

=COUNTIF($AE:$AE,C1)>0

Define formatting.

Unless I am doing it wrong nothing happens? There is an option to use a formula to determine which cells to format?

 

[Legacy 143009]

Discard it is in Turkish. The idea is the same.

 

[Dazzawm]

Discard it is in Turkish. The idea is the same.

Still nothing. I will explain again. There could be anything from 1 to 10 cells/rows that match in C then it needs to look at AE for duplicate data amongst those rows, not the same as C, other types of data. Then moves onto the next set of rows where values are the same in C and so on...

 

[Dazzawm]

As you can see in the example below the two rows at the top that match in C are clear because there is different data in AE. The next set of 5 matching rows in C are highlighted because there is a duplicate of Data1 in AE.

ASVIR53 5001																														Data1
ASVIR53 5001																														Data2
AU10018 2001																														Data1
AU10018 2001																														Data1
AU10018 2001																														Data2
AU10018 2001																														Data3
AU10018 2001																														Data4

 

[Legacy 143009]

OK try this rule. I tested:

=(SUM(IF(FREQUENCY(MATCH(IF($C$:$C$=C1,$AE$:$AE$),IF($C$:$C$=C1,$AE$:$AE$),0),MATCH(IF($C$:$C$=C1,$AE$:$AE$),IF($C$:$C$=C1,$AE$:$AE$),0))>0,1))-1)<>COUNTIF($C$:$C$,C1)

 

[Dazzawm]

I got this

 

[Legacy 143009]

Could you try again? I forgot extra $ signs:

=(SUM(IF(FREQUENCY(MATCH(IF($C:$C=C1,$AE:$AE),IF($C:$C=C1,$AE:$AE),0),MATCH(IF($C:$C=C1,$AE:$AE),IF($C:$C=C1,$AE:$AE),0))>0,1))-1)<>COUNTIF($C:$C,C1)

 

[Dazzawm]

Could you try again? I forgot extra $ signs:

=(SUM(IF(FREQUENCY(MATCH(IF($C:$C=C1,$AE:$AE),IF($C:$C=C1,$AE:$AE),0),MATCH(IF($C:$C=C1,$AE:$AE),IF($C:$C=C1,$AE:$AE),0))>0,1))-1)<>COUNTIF($C:$C,C1)

I cant say that was very successful, it ran for about an hour and crashed excel. It was a big file to be fair. Is there a macro that will do the same?