Testing Equivalences in Excel

I spend my working life implementing Maconomy, BizView, and other ERP and data analysis systems, and I often have to clean and prepare data sets for importing to database tables. This nearly always involves constructing a clean data set in an Excel sheet – often from more than one source – and for this work there are a number of methods I use time and again.

One of these is the Excel equivalence function, which I use to compare two columns of values. There are more elegant formulae that might accomplish the same goals, but they will take longer to set up and use – in comparison, the method in this article is “quick and dirty”, takes minimal time, and is so simple that it can be discarded after use without having to worry about remembering the syntax or preserving for reuse elsewhere.

Suppose I have two columns of values and I want to test if they are identical, and if they aren’t, where the differences lie.

Let’s say columns A and B contain the values, which cover the area A2:B100.

First insert a new column between columns A and B, and in the cell B2 type “=“, then press the left arrow key, then type “=“, then press the right arrow key.

B2 now contains the formula =A2=C2. This returns a TRUE if both cells hold the same data, and FALSE if they don’t.

Drag or copy the formula down to B100. The column between A and C now indicates if the values on either side match or don’t.

Now apply an autofilter to A1:C100, and you can filter out only those lines where B contains FALSE.

I often use this to confirm I am dealing with two identical columns before I move on (for example, one might be original data and the other might have been exported from a database after import), so I want to be able to insert the column, write the formula, apply and check the autofilter for all TRUE, and then delete the column again. This can all be accomplished in under a minute, and enables me to check my work as I go along.

And, of course, if there are FALSEs, they are easily identified, which is the first stage of troubleshooting.

Two further modifications come in handy. The first is where you are comparing text strings. As described, the equivalence formula will return a TRUE if one string was lower case and the other was the same letters in uppercase. The EXACT function will deal with this – =EXACT(A2,C2).

The second is where you have more than two columns to check. Say the spreadsheet contains two tables of four columns each, separated by an empty column – for example, A2:D8 and F2:I8. In this case, use the following matrix formula: {=AND(A2:D2=F2:I2)}. You don’t type the curly brackets at the beginning and end of the formula, instead you complete the entry with the following key combination Shift+Ctrl+Enter rather than just Enter.

Shift+Ctrl+Enter will convert the formula =AND(A2:D2=F2:I2) into a matrix operation {=AND(A2:D2=F2:I2)} and surround it with curly brackets { … }. If you don’t apply this, the formula will not work.