26-May-2009 NEW UTILITY: Compare 2 Data Sets

Nick's picture



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:

compare-2-data-sets

compare-2-data-sets

When we compare the 2 data sets, we get this: 

compare-2-data-sets

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:

compare-2-data-sets

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.
 

NOTE:

  1. There is no error checking, so if you enter something incorrectly, it will break.
  2. 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:

 

Possible enhancements:
- 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

AttachmentSize
vba-tips-compare-2-data-sets.xls63 KB
Compare1.xls17 KB
Compare2.xls17 KB

One small hiccup and I'm pretty sure my fault

First off, this has the potential to be a huge time-saver. Thank you. Till now, I was using VLookUp and so on. It would take a an hour or two to get everything reconciled even on small project with only 2,000 entries. As a relative rookie to excel, this is awesome.

Here is the issue I am having: I am able to compare to datasets each of 1330 rows fairly quickly, however, one column in particular always comes up as mismatching. I will gladly send you the two files to discuss.

Thank you again.

compare data sets

Happy to get someone to look into your problem..
There is a charge for this though..

Compare Data Sets

Of course. What is your email so we can discuss? Thank you for the help.

contact

hi

hi thx very much this looks extremely useful.

any idea why this line would be failing?

Sht2.Cells(sr2, mcn1.Offset(iRow, 1).Value).Value = sht1.Cells(sr1, mcn1.Offset(iRow, 0).Value).Value

arigato gozaimasu

Nick's picture

Error

I've not made it bullet proof... the most likely reason is because the cell contains an error.

so to fix, do something like:  if iserror(...)  then

 

Hi, I am a VBA n00b. i wasn't

Hi, I am a VBA n00b. i wasn't implying that you are not good at coding but was looking for an explanation of what that line is trying to do so I can try to trouble shoot it.

trying to compare my EOD trading blotter w/ trades we input in the system. this page seems to do exactly what I need. i have both my data sets in the same workbook so maybe this is complicating things..

also, i dont really know how to use if iserror() function so trying to read up.

very grateful for your efforts. thank you

Nick's picture

Comparing Data sets

Data sets need to be in different workbooks...

If your organisation has an IT budget, and you think that this little system  might be able to help, I'd be happy to explore the opportunity to create something customised for your organisation.

Your business case would be that using a system to do your manual work reduces the chance of costly manual errors...

Call:+442081234832

I used to do exactly your job, and I wish it had have been automated, so I could have left early, and my traders would have had their reports earlier !

Rgds

Nick