Compare 2 columns?

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

http://www.cpearson.com/excel/duplicat.htm#InOneNotOther%5B/url%5D

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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

Reply | Quote