Compare Two Lists of Data in Excel Tables

In many tasks you perform on the daily basis you should compare two lists of data. For example, if you have 2 lists of customers with monthly statistics, you should find which new customers were added, which of them are deleted in the current month. Also, possible customer's information was changed and you should reflect it in the another documents. Another widely used example - financial record-keeping. Find changes in the monthly statements, find data which is not reflected in the monthly statements. All these tasks will eat a lot of your working time, if lists of your data contains thousands records.

You can use different methods to compare two lists of data in Excel. For example, you can create a column with Primary Key and use MATCH or LOOKUP functions to build comparison report.

xlCompare makes this procedure much simple. You should not create new columns of data, sort them and insert formulas to find matched records. xlCompare finds Primary Key in your lists. If data in the key column is not unique - this is not a problem. xlCompare can use it as a key. This is complex task, but xlCompare knows how to do it. You should only open lists of your data and select appropriate command on the xlCompare ribbon (Comparemenu, if you are using Excel 2000/XP/2003).

Data should not be sorted before comparison. xlCompare will sort the data itself. It tries to do all possible work for you. In the Comparison Report data is not shown in the sorted order. You see your original lists of records in the report. But Difference Explorer will point you to corresponding records, when you select a difference.

When you compare data using Excel features, you should somehow mark differences. For example, you can use Conditional Formatting, or a set of cells with logical values, to reflect results of the LOOKUP. xlCompare gives you color-coded Comparison Report, where each difference is highlighted with color. So, you should not spend a time to design formatting options and apply them to worksheets. Everything is done by xlCompare.

Lets make a summary. What do you get if you are using xlCompare to compare lists of data.

  • You should not create a column with unique identifier.
  • You should not sort your data to prepare it for comparison.
  • You should not enter new formulas to find matching records.
  • You should not apply formatting options to the found discrepancies.
  • You get comparison results in one click! Just open your data and press Compare button.
  • You get color-coder comparison report.
  • Merge and Edit data directly in the comparison report.
  • You spend only a few second to get the results.
When you are doing comparison yourself, you can make a mistake and this will cost you time and money. Also, you may spend a time to double-check if you've compared the lists properly. xlCompare is tested on the thousands of PCs around the world. It doesn't make a mistakes in comparison.

Into which problems may be trap, and how to resolve them.

Inconsistent abbreviations. You can change the value to the right one directly in the report and run the comparison again using Restard Comparison command. This command is present in the Right Click Menu in Difference Explorer and on the Difference Explorer Toolbar. Few seconds and you have the correct report.

Extra numbers or characters on one list. You can use same solution like for the previous case. Just correct the key values and restart the comparison.

xlCompare tries to make all comparison work itself, you can focus on the analysing the comparison results.