Comparing 2 Large Excel Files

Drawde

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

Dump the contents of Excel2 into Sheet2 of Excel1
Highlight everything in Sheet2, and fill with yellow background
Copy the data of Sheet2 and merge into Sheet1 (sheet2, the newer data, would be yellow, the old data would be white)
Use Excel's advanced filter to remove duplicates
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: September 26, 2008 5:35 PM Last updated: August 20, 2014 3:24 PM

Answer Wiki

Last updated: August 15, 2013 7:53 PM GMT

Michael Tidmarsh62,200 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,200 pts.
- Flame14,925 pts.
- Smlile35 pts.

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(array,MATCH(lookup_value,lookup_array,0))),”NOMATCH”,INDEX(array,MATCH(lookup_value,lookup_array,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

<a href="http://www.formulasoft.com/xlsc_order.html">This program</a> seems to do what your require. You can "try before you buy" and the price seems reasonable. <a href="http://www.comparesuite.com/support/compare_excel_spreadsheets.htm">This is another program</a> that is a little more expensive, but offers a 30 free trial. This is a <a href="http://www.softplatz.com/Soft/Business/Office-Suites-Tools/Excel-Compare.html">Freeware program</a> that might be worth looking at. This is another <a href="http://www.higherdata.com/">Freeware program</a> 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(array,MATCH(lookup_value,lookup_array,0))),"NOMATCH",INDEX(array,MATCH(lookup_value,lookup_array,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

Send me notifications when members answer or reply to this question.

Discuss This Question: 2 Replies

ctincristi Oct 19, 2012 1:16 PM GMT

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 Aug 16, 2013 4:54 AM GMT

If I understood correctly, Compare means, u want to find duplicates....for that purpose u can use conditional formatting/ see here

103,790 pointsBadges:

report