26-May-2009 NEW UTILITY: Compare 2 Data Sets
Compare 2 Data Sets
- Have you ever spent hours reconciling 2 sets of data ?
- Is it your job to reconcile data ?
If yes, then this utility goes out to you. I have done that job, and it's really hard work.... This will get you home early.
You can use this utility for:
- Broker confirmations
- Data reconciliation
- Auditing changes
- Many other things
Here's a screen shot of our 2 sets of data in Excel:
When we compare the 2 data sets, we get this:
If the row is red, it means a missing entry
If a cell is red, but there's green in the row, it means we found the entry, but one of the values was different
- NOTE: We can run the comparison for data
- Starting in different rows / columns
- With different headings
- Choosing the columns we want to compare
Here's a screen shot of the main screen:
There are a lot of entries, but what do they all mean?
Workbook1 - list the full path to the workbook
Worksheet1 - sheet where your data is
Heading Row1 - the row the headings are on
Calculate Last Row From Column - this is the column with the most data
Unique Key Is In Column - this is the thing that you want to compare... in our example, it's the bond
Main Comparison Column - this is used to sort the data to prevent matching things off with the wrong values if there are multiple entries for the Key column
Output File - a link to the output file thats generated
Mapping - column headings do not need to be labelled the same, all you have to do is to specify which columns you want to use in the comparison, and what they map to.
- There is no error checking, so if you enter something incorrectly, it will break.
- To replicate this analysis, save all 3 files to your C drive, and press the "Compare" button.
Training Video on how to Compare 2 Data Sets in Excel:
- Ability to add calculated columns to the files
- Ability to show fields but not compare them
- Easier column mapping
- Ability to select the files through double clicking on the cell containing the file path
- Better error handling
- Excel 2003 version
- Allow Column letters too.. e.g: A,B,C