VBScript to compare 2 excel files and highlight the differences

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.
Imran AsifAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Imran AsifAuthor Commented:
Hi Martin:

Yes a report in a new workbook is fine.

Thanks,
0
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
No this wouldn't happen. Names will not be different in identical rows.
0
Martin LissOlder than dirtCommented:
Or how about the 'Doc Number'? will it always be the same if it's there at all?
0
Imran AsifAuthor Commented:
No, the doc number can be different for the same person (Name).
0
Martin LissOlder than dirtCommented:
OK I'm working on it. It may take a while but I'll get back to you sometime today.
0
Imran AsifAuthor Commented:
Thanks a lot, MartinLiss.
0
Martin LissOlder than dirtCommented:
Almost there.
0
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
Ok, thanks.
0
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
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
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
If I increase the number of columns do I need to modify the script?
0
Martin LissOlder than dirtCommented:
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
Martin LissOlder than dirtCommented:
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
Imran AsifAuthor Commented:
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
Martin LissOlder than dirtCommented:
It's not a part of the original question but here it is anyhow.
Q-28491828.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Imran AsifAuthor Commented:
Perfect, Thanks.
0
Martin LissOlder than dirtCommented:
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
Jatin PoddarCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.