Alex Rivera | Logout

Excel compare two columns and highlight duplicates

Asked 2012-04-04T09:45:38.470
46

I have an excel file with 10,000 rows in column A some values are the same.

Example:

A1 - P7767

A2 - P3443

A3 - P7767

A4 - P8746

A5 - P9435

etc...

I then have another column with 100 rows which have some of the values found in column A,

B1 - P7767

B2 - P8746

etc...

I need to highlight all cells in column A where the value is found in any of the values in column B

So basically column B checks to see if it can find the same value anywhere in column A, if true highlight the cell leaving any cells white when the value is not found in column B

I hope I have explained this well, I have done some research and I believe I need to use conditional formatting to get this result but I am really stuck on the formula to use and cannot seem to find an example online (Maybe I am not searching the correct term as I'm not sure on what this is exactly called)

excel excel-formula
Edit
Report

1 Answer

4

The easiest way to do it, at least for me, is:

Conditional format-> Add new rule->Set your own formula: 

=ISNA(MATCH(A2;$B:$B;0))

Where A2 is the first element in column A to be compared and B is the column where A's element will be searched.

Once you have set the formula and picked the format, apply this rule to all elements in the column.

Hope this helps

answered 2013-01-30T12:12:02.240

Your Answer