No announcement yet.

Comparing two columns and highlighting differences

  • Filter
  • Time
  • Show
Clear All
new posts

  • Comparing two columns and highlighting differences

    I have a running spreadsheet that update every week and I have been comparing the differences by hand. Know that there has to be an automated way.
    Each week I input the new column of values which is usually ~200 entries. I then need to know which values were deleted or added between last weeks and this weeks rows.
    Attached is an shortened example with Sheet1 containing values. Sheet2 is the expected results I am looking for.

    Thank you in advance. This would save me tons of time and frustration.


    Running List.xlsx

  • #2

    Re: Comparing two columns and highlighting differences

    You could use conditional formatting, using the formula option, to highlight the values that are not present in the other lists.

    Using the list in column-B.

    1. Select all cells in the range, beginning with the top most cell
    2. On the Home tab of the Ribbon Menu, click Conditional Formatting > New Rule
    3. In the dialog, choose "Use formula . . " and enter this: =COUNTIF($A$1:INDEX(A:A,),B2)>0
    4. Apply color format of choice

    If there are cells in column-A that contain the exact values as cells in column-B, then the cells in column-B will be highlighted.

    Forum Rules | Message to Cross Posters | How to use Tags