|Check out our Excel Resources|
If you'll paste the below code in the topmost cell of the column you'd like to get comparion results in and then edit the A1 and B1 to reflect the columns in your sheet that you'd like to compare, then an "N" will be returned if the cells aren't equal and a blank space " " will return if they are equal. To compare the whole column, simply highlight the cell with the formula and corner drag as far down as necessary. You can also adjust output by changing the N between the quotes to whatever you want and adding anything between the second set to signify a match.
(essentially, is says if A1 is not equal to B1, then print an "N" else print a blank " ".)
Hope this helps,
1. Select column B (click the letter B)
2. Choose Format > Conditional formatting
" 3. In ""Condition 1"" choose ""Formula is"""
" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<>"")"
" 5. Click the ""Formats"" button and take your pick. OK.OK."
Now every name in B not in A will light up.
comparison of 2 columns
this function put in c1 and autofilled down will show the A1 value,
IF A1 does not exist anywhere in B1:B2000 -
Reply to: Compare two columns using wildcards
where A1 is a value from the 1st column and List2!A:A is
a reference to your 2nd column. This formula will return
0 or 1. 1 means that there's a match; 0 means there isn't.
You can apply a number format to change these values to any
flag you wish. For example, [=1][green]"Y";[red]"N" will
produce a green "Y" or a red "N".