In this video tutorial, we show how to compare two Excel sheets by several key columns and find differences with the Compare Two Sheets tool.
Compare Two Sheets offers three comparing modes for different types of worksheets.
Select the By key columns mode if you have column-organized tables that contain row-by-row data. For example, two price lists with the same "SKU" or "Product ID" columns. Usually, these columns are called "key columns", this is why this comparing mode is called By key columns. Such sheets always have the same number of columns but may include a different quantity of rows.
Compare Two Sheets in Excel from A to Z
How to compare sheets by all columns
How to compare lists in Excel cell by cell
In this video, I will show how to compare two Excel sheets by several key columns and find differences.
So here are my two price lists - Price list 1 and Price list 2. The first column contains the name of the item, in the second I have the package, in the third is the origin of the product, and the fourth and the fifth columns contain prices.
My task is to find differences in these two tables. I will compare the sheets by three key columns: Fruit, Package, and Origin.
You may ask: what is a key column? What do we mean when we use this term and why do we need it at all? The point is that key columns help you compare your datasets in different ways. For instance, if I need to find differences between packages, origins, and prices of the same fruits, I will select the first column - Fruit - as the key column.
The tool will take the value in the Fruit column of the first sheet and search for the same value in the same column of the second sheet. As soon as it finds the matching value, it will consider the found line as the matching row. And it will compare these matching rows, coloring the different cells in green.
So, the key column is the column which's entries are used to find matching and unique rows. The result you get depends on the key columns you select.
I need to find rows that have same values in the columns Fruit, Package, Origin and see the different cells in these rows in other columns. So, my key columns will be Fruit, Package, and Origin.
In the ribbon, I go to the Ablebits Data tab and find the Merge group. I need the Compare Two Sheets tool.
First of all, the utility asks you to get acquainted with the requirements and limitations. To put it in short, please make sure your Excel files are saved, not protected, and there are no merged cells. Besides, the two sheets should have the same layout and structure including grouping, filtering, and sorting. That's it! Let's click Agree to move forward.
On the first step, we should select the sheets to compare. They can be either in the same workbook or in two different files.
This is the step where we specify the comparing mode and the matching option. No worries, it's not as complicated as it sounds. We have developed three comparing modes to make our tool suitable for different types of worksheets.
My sheets are column-organized price lists. As my task is to compare them by key columns, I pick the corresponding mode - By key columns.
Now we are to understand and select the matching options. What is important here is to pay attention to this tiny grey note: For partially matching rows. It means that the matching options work for only rows that are not identical by key columns. In such cases, for a row in the first sheet, the tool may find several possible matching rows in the second one.
Here we have a cheat sheet to help you understand the logic of the matching options better. In these schemes, consider all the three columns as the key ones.
The First match means that the first row that has the same value in the first key column will be considered as the matching one. One and the first match is enough, the tool finds it and colors all the next rows as unique. It doesn't search for any more matches.
The Best match means that Compare Two Sheets will search for the highest number of the same values in the key columns. The row with the maximum matching values is considered the matching row. Other rows are colored as unique.
And the easiest to understand matching mode is the Full match only. The tool will scan all the values in the key columns and if there are no full matches, it will highlight all the rows as unique ones.
Right now, I select the First match option and click Next.
Here we will pick the key columns. As we have already discussed, they are Fruit, Package, and Origin.
And the final step: let's select the comparison options. I leave all of them as they are.
I select Mark differences with a background color, and here are the colors:
Unique rows in Sheet 1 will be colored in blue.
Rows that exist only in Sheet 2 will be highlighted in coral.
Cells that are different in matching rows will be colored in green in both sheets.
You can choose any other hues for your sheets and hit Compare!
Let's see what we've got.
Mango in the Tray package is present in both tables, that's why the tool has identified these two rows as matching. The different cell is in the Origin column, and it is colored in green.
The second row is identical, so nothing is highlighted in both sheets. Look, there are no Grapefruits in the second table, so this row is colored as unique.
Banana is present in both tables without any differences, so nothing is highlighted here.
And here we have two Avocado rows. Despite the fact that the sixth row has more in common with this row then the fifth one - Avocado and Tray - Compare Sheets has decided that the matching row here is this one. Why is that? That's right because we selected the First match option! As soon as the tool has found the first same value in the second table, it stopped the search. Very simple, isn't it?
And thus, the second Avocado row is considered unique and colored in coral.
I cancel the review differences mode and remove difference marks from my original books.
Now let's do the By key column comparison again, but this time select the Best match. Do you have any idea what will happen?
Let's go through the same steps:
Ablebits Data - Compare Two Sheets - Agree - By key columns - Best match - Fruit-Package-Origin - Next - Compare.
All the same as in the previous comparison, despite these two rows. Can you see? Now the tool has found that the sixth row contains more matching values than the previous one, so the fifth row is unique now, and the sixth is the matching row with the only different cell colored in green!
It is really important to understand: the matching options work for only partially matching rows!
If you try to compare sheets that have fully matching rows by all key columns, you will get the same result using both First match and Full match options.
And now let's select the Full match only option and the same three key columns.
Look, we got a different picture. As we have selected Full match only, all rows that have even one different cell are marked as unique.
I hope my explanations have made it a little bit easier for you to understand how to work with Compare Two Sheets and tune it for your comparison needs.
Please do not hesitate to contact us if you have any questions or issues with this add-in!
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
