Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Brooklyn Park, Minnesota, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare 2 columns?

    I want to quickly determine if 2 columns of data are identical. Ideally, I'd like to find some way to highlight the cells in the right column that do not match the left column. Is there a way to do this?

    Thanks!
    DeNae [img]/w3timages/icons/clown.gif[/img]

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Compare 2 columns?

    If this is a one-time review, take a third column & write the formula =a1=b1 (adjusted for your start position), copy it down, copy over the calculation with paste values, search and replace "TRUE" with nothing, and then inspect every row which says "FALSE", since it failed to match.

    If you are working on un-rounded formula number results, you may want to use =round(a1,6)=round(b1,6) to ignore insignificant floating point errors.

    For frequent (or more complex) reviews, merge the two columns and use the Data, Filter, Advanced, Tool.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Compare 2 columns?

    Thanks for reminding me of this approach. Chip has some really good solutions, and I have used this and other of his array formulas in the past, but it is often more than I need.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 2 columns?

    If you select both columns then:

    Edit -> GoTo -> Special -> Row differences

    Excel will select the different entries in one of the columns depending on which column you selected first (select A-B and and differences will select in B and visa versa)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compare 2 columns?

    Select the second column
    Format/Conditional Formatting
    set it to Value is not equal to address of first cell in first column (no $s)
    Click the format button select the Patterns tab and yellow or something
    click ok

    Example. If the first column is A1 to A24 and the second is B1 to B24 the conditional formatting box should say
    Cell Value is | Not equal to| =A1
    David Grugeon
    Brisbane Australia

Posting Permissions

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