When you have two similar Excel workbooks, or better say two versions of the same workbook, what's the first thing you usually want to do with them? Right, compare those files for differences. There were many solutions given on internet, but everyone had their own drawback, e.g. viewing files side by side and then visually finding out the differences, well definitely no one would like to do this at least for the large files, or by using excel formula like =IF(Sheet1!A1<> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, ""), this also not gonna work, if there are multiple sheets doing this all the pair of sheets is a huge task. Also there are softwares which compares two files, I have used few of them but they can compare only one sheet and if there are multiple sheets then one by one we can do it. And many other solutions which I am not going you through. But I was not looking for these kind of solutions, so I came up with this macro to compare two excel files not matter if you have single sheet or multiple sheets, which I am going to show you.
For Demonstration I am using the file which two sheets, Sheet1 and Sheet2 and I have two versions of this file
In sheet1 we have 2 differences as above
and in sheet2 there are no differences
Step 1: Open the Compare_1.4.xlsm macro file (Download it from below link)
Step 2: Select file 1 and file 2, the two files which you want to compare by clicking on open file button
Step 3: Click on Compare Workbooks button and Bammm!!
Number of differences in each sheet will be saved on column T
Sheets from file 1 and file 2 will get copied in the macro file with _1 and _2 suffix and the differences will be highlighted in Red color.
Thanks for reading this blog. Hope this helps!!
Here is the file Compare_1.4.xlsm
New Updated file Compare_1.6.xlsm
Comments
Post a Comment