557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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).
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.
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:
The three arguments are as follows:
|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.
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.
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):
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|
Some other pages relevant to the above blogs include:
25 Aytoun Street