Comparing 2 Large Excel Files

5 pts.
Tags:
Microsoft Excel
VBA
I have 2 large Excel files, one containing yesterday's data, and another containing yesterday's data + today's updates/changes. For registration purposes, I need to find all the differences (ie new rows/entries, a value changed within the row in the new file vs the old file, etc). These excel files have the same basic structure: the columns go from A to CR (96 columns), and around 5000 rows. Basically, I'm looking for an automated way to compare both files, spitting out any and all differences between the old file and the new file to a new sheet/file. Especially: new rows/entries (in excel1 not in excel2, and vice versa), changed values within a row (X in excel1 but Y in excel2, and vice versa). I am open to any and all suggestions, as well as solutions. Just so you have an idea, a more primitive method that kinda does the job::
  1. Dump the contents of Excel2 into Sheet2 of Excel1
  2. Highlight everything in Sheet2, and fill with yellow background
  3. Copy the data of Sheet2 and merge into Sheet1 (sheet2, the newer data, would be yellow, the old data would be white)
  4. Use Excel's advanced filter to remove duplicates
  5. Sort alphabetically A-Z.
Everything yellow would be either a new row/entry, or the updated value of the row above it, and I would manually look for differences.

Answer Wiki

Thanks. We'll let you know when a new response is added.

This program seems to do what your require. You can “try before you buy” and the price seems reasonable. This is another program that is a little more expensive, but offers a 30 free trial. This is a Freeware program that might be worth looking at. This is another Freeware program to try
As always, when downloading ANYTHING from the web check for viruses and spyware.
Good Luck!
-Flame
**************************

What, specifically, is the comparison based upon? There needs to be some index number or value you are comparing within yesterday’s and today’s data. An example would be somebody’s name. With this example, you would lookup person ‘A’ from today and compare to person ‘A’, from yesterday and then compare values. Whatever the index value is, you can use an ISERROR, INDEX ,and MATCH formula combination, as an example,

=IF(ISERROR(INDEX(<i>array</i>,MATCH(<i>lookup_value,lookup_array</i>,0))),”NOMATCH”,INDEX(<i>array</i>,MATCH(<i>lookup_value,lookup_array</i>,0))).

The ISERROR gives a NOMATCH value instead of #NA if no match is found (you can use any text or number value to replace NOMATCH). A conditional format can be added to each cell so that if a NOMATCH value is present, then the cell background changes to a selected color. Anytime you see this background color, you know a change has occurred to that cell.

I compare daily files using this method and it works great. There just has to be some index value to compare the data sets.

If this is confusing, I would suggest referencing the ‘Excel Bible’ book by Walkenbach & Underdahl.\

+++++++++++++++++++++++++++++++++++++++++++

Excel 2007 has a compare feature.

Hope this helps!

-Schmidtw

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following