excel looking through two columns of values and finding values that don't exist in one of the columns

Question

I have two columns of data in my excel spreadsheet - i pulled this data from SQL server but I feel like I might an easier time doing this in excel

ColumnA   ColumnB
123       123
234       234
456       456
678       677
679       679

So basically COLUMN A has all the correct values, so I'd like to use that column as my reference. I want to use the values in ColumnA to find a matching value in ColumnB - not necessarily in the same row, but look through the entire column. If there's no corresponding value then highlight or put in bold the value in ColumnA (since that column has all possible values).

If we assume that ColumnA and ColumnB data are complete ....I'd want to see value 678 highlighted since ColumnB does not have 678.

@pnuts - if i don't have to write code for this then I'd like that better. Just trying to get an idea of how I'd go about it — BobSki

pnuts pnuts · Accepted Answer · 2017-05-03T15:50:31

Please select ColumnA and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(A1<>"",COUNTIF(B:B,A1)=0)

Format..., select your choice of formatting, OK, OK.

excel looking through two columns of values and finding values that don't exist in one of the columns

1 Answers