Use xlCompare to compare two lists of data.
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
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
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.
tries to make all comparison work itself, you
can focus on the analysing the comparison results.