Compare columns in two worksheets, list missing values on third worksheet

Question

I am an Excel rookie with three worksheets in a single file. Each has a column of numbers that I'd like to compare.

If values are present in sheet 1 (about 20 000 rows and 2 columns) but missing from sheet 2 (about 15 000 rows and 1 column) I'd like a list in sheet 3 (which is currently empty) showing both columns from sheet 1.

Is this possible? I've seen many answers that involve side-by-side comparison of rows, but I can't seem to get it to a) work across two sheets, b) output the info onto a totally separate sheet, and c) bring over the second column. I also don't want to have to scroll through 20 000 rows to find which ones are missing! This seems to be a common solution, but would be very frustrating with such a large data set.

Sheet 2 is going to be updated somewhat regularly, so I'd like something in Excel that will just allow me to paste the new data in and see the differences immediately in sheet 3. Otherwise I'd be converting to CSV and running some text tools on it.

I'm using Excel 15 on a Mac, if it makes any difference. Thanks for any assistance!

Sheet 1

        A          B
1    1204200    WINNIPEG
2    1204201    WINNIPEG
3    1204202    WINNIPEG
4    1204203    WINNIPEG
5    1204204    WINNIPEG

Sheet 2

1    1204200
2    1204201
3    1204204
4    1204205
5    1204206

Sheet 3 (as desired)

        A           B
1    1204202    WINNIPEG
2    1204203    WINNIPEG

Answer 1

We must:

• identify the missing items
• gather the missing items

In Sheet1 cell C1 enter:

=IF(COUNTIF(Sheet2!A:A,Sheet1!A1)=0,1,"")

and in C2:

=IF(COUNTIF(Sheet2!A:A,Sheet1!A2)=0,1+MAX($C$1:C1),"")

and copy down:

Column C assigns a unique id to each of the missing items.

Then in Sheet3 cell A1:

=INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!C:C,0))

and in B1:

=INDEX(Sheet1!B:B,MATCH(ROWS($1:1),Sheet1!C:C,0))

and copy these downwards: