Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: Comparing two columns and highlighting differences

  1. #1
    Join Date
    31st August 2010

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    19th January 2007
    South Carolina, USA

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. List Differences Between Columns
    By shailesh1 in forum Excel General
    Replies: 8
    Last Post: August 19th, 2008, 10:03
  2. Matching And Highlighting Columns Of Numbers
    By tonyr313 in forum Excel General
    Replies: 1
    Last Post: February 7th, 2007, 14:19
  3. Replies: 20
    Last Post: July 18th, 2006, 10:59
  4. Replies: 3
    Last Post: June 6th, 2006, 23:37
  5. Highlighting rows and columns for presentation
    By misscrf in forum Excel General
    Replies: 6
    Last Post: December 22nd, 2005, 03:20


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts