Comparing Two Columns of #'s

  • I have two columns of different sets of #'s. I would like to highlight the #'s in Column A that are not in Column B.


    A B
    3 2
    5 4
    7 5
    10 12
    15 13
    20 18
    20


    Is there a way to highlight in color, the #'s in Col A that are not in Column B????


    ANY help would be greatly appreciated!!!!!



    Thank You

  • You also can do the CONDITIONAL FORMATTING directly in cloumn A.


    If your column B entries extend from B1:B7, then select all of the cells in column A and from teh toolbar do:


    FORMAT > CONDITIONAL FORMATTING > Formula Is > and in the formula window enter:


    =ISNA(VLOOKUP(A1,$B$1:$B$7,1,FALSE))


    Then set the formatting you want for cells with no match in column B and click Ok.

  • thomach,


    I tried Jindon's and your formula on my excel worksheet.......it worked great !!!!! Thomach, your formula saved me hours and hours of work ! Thank you very much for showing me how to do it !


    Have a Great Day !!!!!!

  • conditional formatting using vlookup


    Use conditional formating on the first column. For the Formula use vlookup whose table of values is the second column and whose lookup value (1st parameter) is the element of the first column you wish to format. If vlookup does not return #N/A then your element is in the second column, and you can format accordingly. When I tested this I used conditional formatting in column A, values in cells B1 to B6, and the conditional formula for cell A10:
    =NOT(ISERROR(VLOOKUP(A10,$B$1:$B$6,1,FALSE)))
    You can use Copy on cell A10 and then highlighting all or some of column A, Paste Special Formats on the rest.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!