Forum Discussion
Highlight duplicates across multiple columns
I have a worksheet with 5 columns. Each column contains anywhere from 10k to 20k rows. I am looking for a way to highlight the cells that appear in every column. I am thinking this is a formula in conditional formatting. Does anyone have any suggestions?
2 Replies
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.
=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.
