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
Microsoft ExcelVB ScriptVBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Professor J

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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
Kanti Prasad


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)

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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Martin Liss

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