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.
NOTE:
- 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:
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
| Attachment | Size |
|---|---|
| vba-tips-compare-2-data-sets.xls | 63 KB |
| Compare1.xls | 17 KB |
| Compare2.xls | 17 KB |
- Nick's blog
- Login or register to post comments
- 26162 reads
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
http://www.excelexperts.com/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
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
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