VBA Macro to compare two excel Worksheets

mruff
mruff used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
Commented:
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

Author

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
Top Expert 2015

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

Author

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!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you supply a workbook with two sheets, each having a few rows of data  showing the highlighted differences?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial