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.
Asked: Last updated:
Related Questions
1

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: 4  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.
  • ctincristi
      Hi, I also wrote an article about comparing two data sets using Excel, text editor or database engine. If you would like to check it out, here is the link:
    http://efficient-work.blogspot.com/2012/10/compare-two-sets-of-data.html
      Feedback is welcomed!
    10 pointsBadges:
    report
  • Subhendu Sen
    If I understood correctly, Compare means, u want to find duplicates....for that purpose u can use conditional formatting/ see here
    141,290 pointsBadges:
    report
  • Synkronizer
    According to me, There are many options to compare two Excel Files:

    1 Using Excel vlookup Formula 
    2 Using Java Code
    3 Using VBA Macros 
    4 Using Some Tools Like Synkronizer
    https://www.synkronizer.com/
    20 pointsBadges:
    report
  • Synkronizer
    According to me, There are many options to compare two Excel Files:

    1 Using Excel vlookup Formula 
    2 Using Java Code
    3 Using VBA Macros 
    4 Using Some Tools Like Synkronizer
    20 pointsBadges:
    report

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.

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

Following

Share this item with your network: