Highlight duplicates across multiple columns

ericjhanna 

Let's say the names are in columns A, C, E, G and I, starting in row 2.

Select A2:A20000.

A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($A2<>"",COUNTIF($C$2:$C$20000,$A2),COUNTIF($E$2:$E$20000,$A2),COUNTIF($G$2:$G$20000,$A2),COUNTIF($I$2:$I$20000,$A2))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.

Select C2:C20000.

C2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($C2<>"",COUNTIF($A$2:$A$20000,$C2),COUNTIF($E$2:$E$20000,$C2),COUNTIF($G$2:$G$20000,$C2),COUNTIF($I$2:$I$20000,$C2))

Click Format...
Activate the Fill tab.
Select the same highlight color.
Click OK twice.

Similar for G2:G20000 and I2:I20000.

ericjhanna 

=AND(COUNTIF($A$1:$A$26,A1)>0,COUNTIF($B$1:$B$26,A1)>0,COUNTIF($C$1:$C$26,A1)>0,COUNTIF($D$1:$D$26,A1)>0,COUNTIF($E$1:$E$26,A1)>0)

Maybe with this rule for conditional formatting which seems to work in my sheet. The formula can be adapted to more rows as required.