This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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). How to compare two Excel worksheets (three methods) Comparing two worksheets using Excel formulae (this blog) Comparing two worksheets using Query Editor Compare worksheets using Inquire or Spreadsheet Compare

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).

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

Posted by Andy Brown on 01 June 2017

In this blog

Joining all of the values together
Creating range names
Finding the differences

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 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:

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:

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:

Use the MATCH function to return the position of this key in the set of all keys in the other worksheet.
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.

Parts of this blog
How to compare two Excel worksheets (three methods) Comparing two worksheets using Excel formulae (this blog) Comparing two worksheets using Query Editor Compare worksheets using Inquire or Spreadsheet Compare