Link to home
Start Free TrialLog in
Avatar of mruff

asked on

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
Avatar of Professor J
Professor J

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mruff


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:
2. WB
->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

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)
Avatar of mruff


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
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!
Can you supply a workbook with two sheets, each having a few rows of data  showing the highlighted differences?