How to quickly check if two columns in Excel are equivalent in value?

Question

I am interested in taking two columns and getting a quick answer on whether they are equivalent in value or not. Let me show you what I mean:

So its trivial to make another column (EQUAL) that does a simple compare for each pair of cells in the two columns. It's also trivial to use conditional formatting on one of the two, checking its value against the other.

The problem is both of these methods require scanning the third column or the color of one of the columns. Often I am doing this for columns that are very, very long, and visual verification would take too long and neither do I trust my eyes.

I could use a pivot table to summarize the EQUAL column and see if any FALSE entries occur. I could also enable filtering and click on the filter on EQUAL and see what entries are shown. Again, all of these methods are time consuming for what seems to be such a simple computational task.

What I'm interested in finding out is if there is a single cell formula that answers the question. I attempted one above in the screenshot, but clearly it doesn't do what I expected, since A10 does not equal B10.

Anyone know of one that works or some other method that accomplishes this?

Answer 1

This is the sort of thing array formulas were designed for. You just need one cell, one formula to give you a comparison:

=AND(A2:A11=B2:B11)

Enter the formula by pressing Ctrl+Shift+Enter. If done correctly it will appear as the following:

{=AND(A2:A11=B2:B11)}

EDIT:

For a case-sensitive comparison, the equals operator will not work. Instead, the EXACT() function should be used. Also enter this as an array formula.

{=AND(EXACT(A2:A11,B2:B11))}

Answer 2

You can use the =AND() function on the third column. This function will return TRUE only if all the entries in the third column are true, otherwise it will return FALSE.

Answer 3

I know this is an old question but here is my answer. If the two columns contain numbers only, then you can just find the difference between the numbers. If all the results are zero, then the two columns are equal. If not, then you'll have a non-zero number at the location.

Answer 4

As long as you have the “EQUAL” column in Column C, you can use =MATCH(FALSE, C:C, 0) to find the first row that contains a FALSE; i.e., the first mismatch.

Answer 5

You can also (assuming data1 is in column A and data2 is in column B) enter a very simple formula below for a true/false if the 2 cells are equal in Column C. Column C: "=A1=A2"

Answer 6

I do this by making the equal column a 0 if equal and 1 if not. Then you can sum the third column.

Answer 7

Paste the code at the bottom into the column you want to display the equality result.

The comparison operator for not equal to is <>. It is used like this:

=IF(A1<>B1, "not equal", "equal")

For more information, see this about.com explanation.