How to quickly check if two columns in Excel are equivalent in value?
Posted
by
mindless.panda
on Super User
See other posts from Super User
or by mindless.panda
Published on 2011-06-24T15:35:21Z
Indexed on
2011/06/24
16:25 UTC
Read the original article
Hit count: 233
microsoft-excel
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?
© Super User or respective owner