Compare two excel sheets

Question

How do I compare two excel sheet and determine which column is missing?

(I would like to compare a list of countries from sheet A with sheet B, then mark which country is missing)

Note: They are in random order.

Solution

You can use the VLOOKUP function in an Excel worksheet to help finding "missing" data in a different sheet. For example, take the following two worksheets:

Sheet1
------
       A          B         C
1     aa 
2     bb
3     cc 
4     dd

.

Sheet2
------
       A          B         C
1     aa 
2     bb
3     dd 

Add the following formula to cell B1 in Sheet and drag the formula down through cell B4:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$A$3,1,FALSE)),"MISSING FROM OTHER SHEET","")

Sheet1 should indicate items that are missing from the other sheet in column B, like so:

Sheet1
------
       A          B                        C
1     aa 
2     bb
3     cc         MISSING FROM OTHER SHEET
4     dd