[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBScript to compare 2 excel files and highlight the differences

Posted on 2014-08-06
26
Medium Priority
?
4,716 Views
Last Modified: 2016-10-07
Hi Experts:

I have 2 excel files.  There are 10 columns in each file.  Columns in file 1 and file 2 are the same but the data is slightly different.  we need to find the differences.

Read the value in column A and compare the values of that row in file 1 and file 2 and highlight the cells which have different values.

Is it possible to compare the 2 files and highlight (color) the rows or cells with a difference?  
Is it possible to reach to a solution with a vbscript?  

Thanks in advance.
0
Comment
Question by:Imran Asif
  • 14
  • 11
26 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40244031
What kind of differences are there? In other words could there be more rows in file 1 than in file 2, or is it just data differences.

Do you want the differences highlighted in both workbooks?
0
 

Author Comment

by:Imran Asif
ID: 40244050
The differences can be any file having more rows and data differences.

It would be nice if the differences are highlighted in both workbooks.  I am attaching a sample file which has data in 2 worksheets.

Thanks,
Sample-data.xlsx
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40244213
If the number of rows where the same it would be a relatively easy task, but with different numbers of rows it is pretty difficult, but let me see what I can do. Rather then highlighting the differences in the actual workbooks, would a report in a new workbook be acceptable?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Imran Asif
ID: 40244225
Hi Martin:

Yes a report in a new workbook is fine.

Thanks,
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40244236
One more question. Can the names be different in an otherwise identical row? In other words could this happen?

In file 1:
2330077      Adler, Jorge      9339710551      S      STDNT      Y      Write Off Check      05-14-2014 0:00      14.25

In file 2:
2330077      Adler, J         9339710551      S      STDNT      Y      Write Off Check      05-14-2014 0:00      14.25

I need some way to tell if a row is missing/extra and so if the answer is 'yes' then I don't know if I can do this.
0
 

Author Comment

by:Imran Asif
ID: 40244245
No this wouldn't happen. Names will not be different in identical rows.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40244247
Or how about the 'Doc Number'? will it always be the same if it's there at all?
0
 

Author Comment

by:Imran Asif
ID: 40244254
No, the doc number can be different for the same person (Name).
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40244406
OK I'm working on it. It may take a while but I'll get back to you sometime today.
0
 

Author Comment

by:Imran Asif
ID: 40244475
Thanks a lot, MartinLiss.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40245069
Almost there.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40245128
OK here's a workbook that contains several macros. I changed some data, added some rows and added a couple of backup sheets for testing purposes.

TestCompareWorksheets
This is the macro you should run (after possibly changing the sheet names) to see the differences in the two sheets. The code for this macro a is copied from http://www.exceltip.com/cells-ranges-rows-and-columns-in-vba/compare-two-worksheets-using-vba-in-microsoft-excel.html

Note that the macro is set up to either compare sheets in the same or different workbooks with the latter commented out.

CompareWorksheets
This macro does the work. It is highly modified but based on the same URL as TestCompareWorksheets. Note that the differences are shown with yellow highlights on the two sheets rather than in a separate workbook.

SortSheets
This macro is called by CompareWorksheets and I added it because otherwise the amounts in rows 1 and 2 (and perhaps others) would have been flagged as different simply because they are in different orders.

RemoveColor
When run, this macro removes the yellow (or any other color) highlighting. It's use is optional.

Restore
I used this macro during early testing to replace the sheets with their backups when I messed up the originals.
Q-28491828.xlsm
0
 

Author Comment

by:Imran Asif
ID: 40245413
Hi MartinLiss:

Thanks for all the help.

From the file you have attached, when I run the VBA, it runs for ever.  Do I need do do anything like deleting the sheets or renaming the sheets?

If I replace the actual data in sheet1 and sheet2 can I run the macro without modifying anything in the script?

Thanks,
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40246223
I tested the code before I uploaded the workbook and it worked fine. When I run it now however it gets into an infinite loop. Let me see what I did to cause the problem.
0
 

Author Comment

by:Imran Asif
ID: 40246338
Ok, thanks.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40246585
There is code in the workbook that depends on knowing where the last row is on each sheet, and sometimes Excel thinks that a blank row really isn't blank and an incorrect 'last row' value is returned. In our case that resulted in the sort placing a blank row after the headings in Sheet2 which threw the sheet comparison off resulting in the infinite loop. I've corrected the problem by adding code to remove any blank lines after the sort.

And to be clear, you don't need to do anything except run TestCompareWorksheets. And you can delete the backup sheets and the 'Restore' macro if you like.

If you have any more problems with the real data could you attach the real workbook(s)?
Q-28491828.xlsm
0
 

Author Comment

by:Imran Asif
ID: 40246663
Hi MartinLiss:

The VBA is quick like lightning.  

One last thing: Is it possible to identify rows that are not common in both sheets?

Thanks,
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40246673
Unless I misunderstand what you mean, it already does that. I'm referring to rows 14 and 15 on Sheet1 and row 8 on sheet2.
0
 

Author Comment

by:Imran Asif
ID: 40246704
If I increase the number of columns do I need to modify the script?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40246745
Yes. You would need to change the following in the CompareWorksheets macro.

Const maxC As Integer = 9

It's '9' now because there are 9 columns (A to I).

And if the new column(s) are added before the 'Amount' column, you'll need to change each of the several Range() statements in the SortSheets macro that refer to column 'I' which currently is that column.

BTW, would you like a stronger indication on the "not common" rows like a different color and/or the whole row being highlighted and/or a comment in column J?

I'm going out for a few hours so I won't be able to respond until I get back.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40247434
I have a modification that automatically determines the last column and does the secondary sort on the "Amount" column no matter where it is. Since I think I've satisfactorily answered this question I'd appreciate a new question for the update, but either way it's yours.
0
 

Author Comment

by:Imran Asif
ID: 40247608
Hi MartinLiss:

Do you have this in place,"I have a modification that automatically determines the last column and does the secondary sort on the "Amount" column no matter where it is."

Can you please post the macro?

Thanks.
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40247618
It's not a part of the original question but here it is anyhow.
Q-28491828.xlsm
0
 

Author Closing Comment

by:Imran Asif
ID: 40249187
Perfect, Thanks.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249210
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 

Expert Comment

by:Jatin Poddar
ID: 41833139
Hi Martin Liss ,

Great work !!

It worked for me Thankyou ,I am a newbie to VBA scripts,  just one thing can you modify the script for the extra rows to be highlighted too in sheets if it is extra , it would be a great help for me .

Thanks .
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question