VBA Macro to compare two excel Worksheets

Hello Experts,
I am searching for a VBA that compares two excel Worksheets, either in the same or in two different workbooks and highlights the differences between the two worksheets.
All columns must be compared
The data rows are not sorted in the same order in the two worksheets
The columns however are in the same order
A solution that highlights the differences in the two working sheets is very appreciated.
It must be VBA and not 3rd party etc. Software as no additional Software can be installed
Many thanks for providing such a macro
mruffAsked:
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.

ProfessorJimJamCommented:
what about VBA to compare two sheets in the same workbook. not in different workbook?  this would be easy and shorter code. i can write this code for you if you want worksheet compare becuase it is very simple. but i do not have time now to write for the one that can do workbooks.

alternatively,

someone already written a very cool VBA code in VBAexpress website you can find the ready VBA with its example here
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
mruffAuthor Commented:
Comparing two sheets in the same workbook would be fine too.
Important: The rows in the 2 workbooks are NOT in the same sort order
e.g. 1. WB:
A
B
C
2. WB
B
C
A
->This should NOT result in differences
It must work with data where the sort order is NOT the same in the two work books

Thank you
0
Kanti PrasadCommented:
HI

First sort your Sheet 1 and Sheet 2 with the sequence sheet 1 with A B C and sheet 2 with B C A .

Then in sheet 3 if you put the below formulas in cell A B and C respectively
Copy them down till end of your cells

you will be able to compare sheet 1 and sheet 2 -- if both are same you will have all columns as TRUE .


=IF(Sheet1!A1=Sheet2!B1,TRUE,FALSE)      =IF(Sheet1!B1=Sheet2!C1,TRUE,FALSE)      =IF(Sheet1!C1=Sheet2!A1,TRUE,FALSE)
0
mruffAuthor Commented:
Hi Kanti,
I require a generic solution with as MINIMAL manual work as possible.
Apply a first sort is not applicable
Inserting a formula is not applicable
I require a VBA solution as automated as possible
Reason:
1. Many excel sheets have to be compared
2. The source data has to be loaded from a csv file
3. This process will be repeated
Therefore I am looking for a solution where:
1. User just loads two csv files into two different work sheets
2. Launches Macro that highlights the differences
Thank you!
0
Martin LissOlder than dirtCommented:
Can you supply a workbook with two sheets, each having a few rows of data  showing the highlighted differences?
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
Microsoft Excel

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.