What would you do if there are two big Excel worksheets with different data types and you have to compare them quickly and efficiently? Indeed, there is the Sheet comparison feature in latest version of Excel (Excel 2013 and higher), but what if you have to make it available for many other testers who have older versions?
Well, many think that there is no way other than using VBA and creating Macros. I say, that would be my last choice. Excel macros are not anti-virus friendly and some users won't or even can't use macros because of that. Also many users have limited knowledge of Excel, hence we can't expect them to run or probable modify macros for their needs.
I use macros only if cell data must get processed, converted and re-shaped to be comparable with the other sheet. If I have to create a macro, will try my best to make it as simple as possible, and accessible easy (probably by implementing buttons in the sheets).
There is another way, however, that won't need to use macros. Here I give you some examples of how we can compare two sheets using IF function in Excel Formulas.
As you know, with IF function we can examine some criteria and the set cell content based on that examination. for example we want to set A1 to True if 2+2=4 Else set the cell value to False:
IF(2+2=4,True,False)
Now assume we have Sheet1 and Sheet2 like this:
Sheet1:
| Name | datetime | Score | |
| John | 4/29/2014 0:00 | 17 | |
| John | 4/29/2014 1:00 | 11 | |
| Mike | 4/29/2014 2:00 | 19 |
Sheet2:
| Name | datetime | Score | |
| John | 4/29/2014 0:00 | 18 | |
| Mike | 4/29/2014 1:00 | 12 | |
| Mike | 4/29/2014 2:00 | 19 |
We can create the third Sheet to compare each row properly:
For normal textual content such as "Name" or even "Hour" column use a formula like this in topmost cell and drag it down to where you think maximum number of the rows of comparing sheets:
will be:
=IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,CONCATENATE("False: ",Sheet1!A1," <> ",Sheet2!A1)
If you have date and time (datetime) in the column: You can even compare specific pieces of the Date/Time such as Hour, Day or Minute by formatting it as TEXT()
=IF(Sheet1!B1=Sheet2!B1,TEXT(Sheet1!B1,"m/d/yyy H:mm"),CONCATENATE("False: ",TEXT(Sheet1!B1,"m/d/yyy H:mm")," <> ",TEXT(Sheet2!B1,"m/d/yyy H:mm")))
The outcome will be something like:
For normal textual content such as "Name" or even "Hour" column use a formula like this in topmost cell and drag it down to where you think maximum number of the rows of comparing sheets:
will be:
=IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,CONCATENATE("False: ",Sheet1!A1," <> ",Sheet2!A1)
If you have date and time (datetime) in the column: You can even compare specific pieces of the Date/Time such as Hour, Day or Minute by formatting it as TEXT()
=IF(Sheet1!B1=Sheet2!B1,TEXT(Sheet1!B1,"m/d/yyy H:mm"),CONCATENATE("False: ",TEXT(Sheet1!B1,"m/d/yyy H:mm")," <> ",TEXT(Sheet2!B1,"m/d/yyy H:mm")))
The outcome will be something like:
Sheet3:
| Name | datetime | Score |
| John | 4/29/2014 0:00 | False: 17 <> 18 |
| False: John <> Mike | 4/29/2014 1:00 | False: 11 <> 12 |
| Mike | False: 4/29/2014 2:00 <> 4/29/2015 2:00 | 9 |
It's almost done. Do you want it more professional and to be able to find diffs faster? use conditional formatting feature for all cells. Something like:" If Cell Value contains 'Fail' then Format it to this bg color". Here is how it would look after:
| Name | datetime | Score |
| John | 4/29/2014 0:00 | False: 17 <> 18 |
| False: John <> Mike | 4/29/2014 1:00 | False: 11 <> 12 |
| Mike | False: 4/29/2014 2:00 <> 4/29/2015 2:00 | 9 |
This would help a lot for big worksheets, MUCH FASTER THAN MACROS!
