Sheetmatcher for Accountants

Introduction

Even though anyone can benefit from Sheetmatcher, accountants are probably the ones who can save the most time using it.
Comparing files like accounting transactions or master data is a very common task, and it can be perceived as doable and not particularly difficult. The reality is that it is a very time-consuming task if analyzed properly, and it is very easy to make mistakes. One single mistake can lead to a restart of the whole process, and this is not acceptable.
Let's clarify immediately what we mean by "comparing files". It is not just about checking if the number of rows is the same or if the total amount is the same. It is about checking if the data is the same, and if not, what the differences are. I want to have, in the end, a clear overview of the data with differences highlighted and the ability to download the results in an Xls file. I want to know for each difference, what is the value in the first file, and what is the value in the second file. Let's see how this can be achieved with Sheetmatcher and how it compares with the traditional way of doing it in Excel.

1. Prepare your files..by doing nothing!

Let's say you have two files, containing a list of transactions: one is coming from an external partner, and the other is your internal list. Proceeding in Excel, you would first carefully check if the columns are in the same order in both files. This is important to save time later when using the VLOOKUP formula, as the column index needs to be specified. Once you have the columns in the same order, you can simply use an increasing number that matches the column positions to be used as the first parameter of the VLOOKUP formula.
Not doing this will require you to manually select the columns to be used in the formula, and this is a waste of time that grows exponentially depending on how many columns you want to compare.
But this is not the case with Sheetmatcher. You can just drag and drop the two files, and the tool will take care of the rest. The order of the columns is not important, as the names of the columns are used to match the data.
FACT: Preparation time: 0 seconds with Sheetmatcher vs at least 5 minutes in Excel.

2. Did you say Key?

Yes, the Key column is the column that will be used to match each of the rows of the two files. You cannot skip this step, as it is the most important one.
Let's say you have a list of transactions, and you think you can match them based on the transaction number. While this is a good idea, you should also consider that each transaction might be split into more rows, depending on the number of items/accounts in the transaction. Given that, you should consider using a combination of columns, like the transaction number and the item number/account. Doing this in Excel would require you to create a new column that concatenates the two columns, and then use this column as the Key column.
Using Sheetmatcher, you drastically simplify this task, as you can just select the two columns, and the tool will take care of the rest. What's more, if you forget to do this and select only the transaction number, the tool will warn you that the Key column is not unique, and you should consider adding more columns. Last but not least, you can use as many columns as you need to build your Key on the fly.
FACT: Building Key time: 10 seconds with Sheetmatcher vs an average of 5 minutes in Excel.

3. I see some results coming

Moving forward into the parallel between Excel and Sheetmatcher, the first thing you will generally want to know is if the number of rows in the two files is the same. This is obviously a very important check, as if the number of rows is significantly different, you will have to understand why before moving forward.
In Excel, this is done by simply performing a VLOOKUP over the Key column(s) of the first file, and then checking how many "not found" records there are. If the number of missing records is plausible, you can proceed to check the quality of the data.
At this stage, there are literally hundreds of ways to do this, and not all of them are correct. What I've seen in the past, for example, is that some people tend to sort the data to compare the two files as if they were reviewing them on paper. Of course, you should not do this, as the data should be compared as it is. By using the VLOOKUP formula, you can return the value from the other file that belongs to the same key, and specify which column you want to compare. The next step after returning the value is to compare it with the original value and receive feedback, such as a keyword indicating if the data matches, like "OK" or "DIFF".
Are you satisfied with this? I am not, because I want to see the data clearly. I want to see the two values side by side when they don't match, and perhaps just an empty cell when they do.Clarity is key, which is why I also like to highlight differences in color, so I can spot them immediately. All of this stuff means combining multiple formulas and conditional formatting, and it is a very time-consuming task. If you want to go deeper on all this steps, i encourage you reading the article I published on Medium about this: Best ways to compare Excel files in 2024.
It should be clear now, that comparing files is not a task that can be done in clicks, especially if you have a lot of data to compare.
FACT: Total time: under 1 Minute with Sheetmatcher vs anywhere from 10 minutes to an open-ended amount of time in Excel.