I need to highlight duplicates between two columns, but not duplicates within the same column.

Question

I need to highlight duplicates between two columns, but not duplicates within the same column.

I've tried all sorts of conditional formatting with various formulas but nothing works.

Answer 1

For Column A

=COUNTIF($I:$I,A2)

For column I

=AND(COUNTIF($A:$A,I2),COUNTIF($I$2:I2,I2)=1)

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Answer 2

Let's say your data are in A2:A100 and in C2:C50.

Select the first range A2:A100.

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

=COUNTIF($C$2:$C$50, $A2)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Next, select the other range C2:C50.

C2 should be the active cell in the selection.

Follow the same steps as for the first range, but with the formula

=COUNTIF($A$2:$A$100, $C2)

Answer 3

Hi Hans,

Thank you for answering so quickly. I appreciate it.

The formulas worked but only on one column.

So the columns are A and I that are being compared.

Column I has several names that are duplicated, but I only want one of them to highlight.

Column A doesn't have any duplicates.

Column A did highlight the names I needed, but nothing highlighted at all in column I.

I'd attach the file but I'm not seeing where that's an option.

Any suggestions?

Answer 4

THANK YOU SOOOO MUCH!!

Answer 5

You are welcome:) You may also give feedback on it.