Goal-  Comparing Tables using Excel

Solution-

1. Make sure that are no trailing or extra spaces. One can either use find and replace function to remove the spaces or Trim or Clean Function.

2.
A              B                    C                D

Names	Capability	Location
John	Testing	A1
Tom	SAP	A2
Tim	Testing	A1
Alan	Java	A2
Alex	Testing	A1
Ron	Mainframe	A3
Dan	Oracle	A5

Table1 is in Sheet1

A              B                  C                 D

Name	Capability	Location
Dan	Oracle	A5
Tom	Testing	A2
Tim	Testing	A1
Alan	Java	A2
Ron	Mainframe	A3
John	Testing	A1

Table2 is in Sheet2.

I have considered Table2 as my reference table and made the comparison accordingly. 

3. To compare use the function COUNTIFS on an empty cell in column D of sheet1 as follows:

=COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,Sheet1!B2,Sheet2!C:C,Sheet1!C2)>0

Here my First Range is the First Column (Name Column) of the Table2 and my First criteria is the cell containing the value John in Table1.
Second Range is the Second Column (Capability) and Second criteria is the cell containing the value Testing in Table1.
Similarly, Third Range is the Third Column (Location) and the Third criteria is the cell containing the value A1 in Table1.

4. The idea is to compare the Name, Capability and Location details of John existing in Table1 with that in Table2. If they match then the COUNTIFS Function returns the count which is nothing but the no. of times the criteria gets satisfied. In our case that will be 1 as there is only one record of John in Table2 matching with the record in Table1.

>0 part lends us a TRUE or FALSE Statement.

If TRUE statement is the output then there are records in Table2 matching with the ones in Table1 else they are not present.

This simplifies the task of Comparison considerably.

5. Just drag and drop the fill handle ( + sign) from the cell in which you got TRUE\FALSE for the first comparison in column D for comparison of the entire table. You don't have to write the formula again.

THATS IT!!

Final Output for the above tables will be as follows:

A              B                    C               D

Names	Capability	Location	Compare
John	Testing	A1	TRUE
Tom	SAP	A2	FALSE
Tim	Testing	A1	TRUE
Alan	Java	A2	TRUE
Alex	Testing	A1	FALSE
Ron	Mainframe	A3	TRUE
Dan	Oracle	A5	TRUE

And do copy and paste special so that values remain and not the formula.

6. Time to Run Home Early :-D

If you want to get your output as quickly as possible using excel then learn to use it Smartly.
Eg. If Find and Replace removes all the spaces in a cell then do not spend time on using the trim function on each column.

There can be times when you have to meet a specific deadline and you don't have time to explore Excel.
This may lead to long working Hours and lot of frustration. It has happened with me.
But while at home I try to learn as much as possible and try to remember it so that the next time I am given an Assignment I deliver it before time :D
No one can run away from Excel (at least in IT) and the moment you delve into it you would want to know more!!