Three different ways to find the differences between two Excel worksheets
Part two of a four-part series of blogs

There are a few ways to find the differences between two Excel worksheets. This blog considers 3 methods (using Excel formulae, using Query Editor and using the Inquire add-in).

  1. How to compare two Excel worksheets (three methods)
  2. Comparing two worksheets using Excel formulae (this blog)
  3. Comparing two worksheets using Query Editor
  4. Compare worksheets using Inquire or Spreadsheet Compare

Posted by Andy Brown on 01 June 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Comparing two worksheets using Excel formulae

The first way to compare two worksheets is to create a unique key for each of them, by combining the values for all of the columns using the TEXTJOIN function, then use MATCH to find the values in one sheet which don't exist in the other.

Note that the TEXTJOIN function was only introduced in Excel 2016 - for earlier versions of Excel you'll have to concatenate the values instead.

Joining all of the values together

Here's what the formula will look like for the first worksheet:

The first formula

The TEXTJOIN functions joins all of the values in cells A2 to H2 together, using # as a delimiter.

Here's the formula used:

=TEXTJOIN("#",TRUE,A2:H2)

The three arguments are as follows:

Argument Use
1 The delimiter to use (so we'll stick a # sign between each pair of values)
2 Whether to include blank cells or not (there aren't any for this example, so it's a bit irrelevant)
3 The cells whose values we want to join together

You can then repeat this for the second worksheet, to end up with two columns of key values.

Creating range names

To make the formulae easier to understand, I've created a range name on each sheet as follows:

Range name created

Select the cells shown, type FirstKey in the range name box shown at top left and press Enter to create this (or any other) range name.

Finding the differences

Having created formulae and range names for each of the two sheets to be compared as shown above, you can now create a function to see if the key for each row in one sheet exists in the other:

The MATCH function

This function checks whether the key in one worksheet exists in the set of keys from the other.

Here's the formula used above (and copied down):

=IF(ISNA(MATCH(I2,SecondKey,0)),"No match","")

That is:

  1. Use the MATCH function to return the position of this key in the set of all keys in the other worksheet.
  2. If this returns an error, it means the key doesn't exist, and we have found a difference (in which case display No match).

The main drawback to this approach is that while you can easily see which rows are different, you can't see why they're different.

You can download a copy of the formulae used here.

This blog has 0 threads Add post