Search code examples
excelexcel-2007comparevba

Compare two excel sheets

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