compare 2 columns for matching data

[bradjedis]

Hey folks,

I have 2 columns of information, where I need to compare the data for matches. Assume Column A is the Prime, and column B is the secondary list. If the data in col A is in the col B list, then highlight cell in col A. Both cols are variable in length, and number of chars in each cell are variable.



Thanks,
Brad

[HansV]

Select the data in column A. I'll assume that A1 is the active cell within the selection, and that the secondary list is B1:B200.

Excel 2003 or before:
Select Format | Conditional Formatting...
Select Formula Is from the first dropdown.
Enter the following formula in the box next to it:

=NOT(ISERROR(MATCH(A1,$B$1:$B$200,0)))

Click Format...
Set the formatting that you want.
OK your way out.

Excel 2007 or later:
Click Conditional Formatting in the Styles group in the Home tab of the ribbon.
Click New Rule...
Click "Use a formula to determine which cells to format".
Enter the following formula in the box that appears:

=NOT(ISERROR(MATCH(A1,$B$1:$B$200,0)))

Click Format...
Set the formatting that you want.
OK your way out.

[bradjedis]

Thanks Hans, that did the trick