Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

enter image description here

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?

share|improve this question

6 Answers 6

up vote 16 down vote accepted

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))}
share|improve this answer
    
That works perfectly and was exactly what I was looking for, thanks. –  mindless.panda Jun 24 '11 at 17:49
    
Only caveat I'll add after using this method is it ignores casing on text values. Not a problem for what I'm doing, but something to be aware of. –  mindless.panda Jun 26 '11 at 19:27
1  
Right you are. I've edited my answer to include a case-sensitive solution. –  Excellll Jun 27 '11 at 13:58

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.

share|improve this answer
    
I like this method because the third column is usually my goto method. Using =AND() makes identifying if there is a difference much faster. However it still requires that pesky third column. I'm really interested if this can also be avoided. I get the feeling it requires an array formula, but I woefully inexperienced in those. –  mindless.panda Jun 24 '11 at 15:45
    
The only other solution that I could think of would be a macro –  EBGreen Jun 24 '11 at 15:47

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
This is effectively the same as Greg’s answer, from two years ago. –  Scott Jul 26 '13 at 2:05

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:

enter image description here

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

For more information, see this about.com explanation.

share|improve this answer
    
Please provide some explanation about what the code does and how to use it. Please see the help section for more info on how to write better answers. –  Paul Sep 5 '13 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.