compare two excel sheet and print the difference?

I have two excel sheet (sheetA and sheetB) and I need to compare one column from sheetA with sheetB and print the difference.

sheetA

Account Number  |    Status
----------------|-------------
876251251       |    Not Active
716126181       |    Not Active
888281211       |    Not Active
666615211       |    Not Active
787878787       |    Not Active
111212134       |    Not Active

sheetB

Customer Number 
----------------
876251251
716126181
792342108
792332668
666615211
760332429
791952441
676702288

I need to compare “Account Number” with “Customer Number”.

• All account numbers from sheetA should be present in customer number in sheetB and if sheetB is missing any “account number” then we should print out those info.
• And all those extra customer numbers in sheetB that are not present in sheetA account number, I want to print that as well.

I was reading about awk and I think we can it using awk but not able to understand how to do it?

$ awk -F'|' 'NR==FNR{c[$1$2]++;next};c[$1$2] > 0' sheetB sheetA

Output should be:

Account Number:
888281211
787878787
111212134

CustomerNumber:
792342108
792332668
760332429
791952441
676702288

Answer

VlookUp will work for this kind of matching.

=VLOOKUP(A2,B2:B9,1,FALSE)

Matching numbers from column “AccountNumber” with “customer number” and resulting the matched numbers of column 1. Filter out N/A (Not matched) you will get all numbers which are not matched from column 1.

For Different sheets

=VLOOKUP(A2,Sheet1!$A$2:$A$7,1,FALSE)

Considering sheet 1 is ACnumbers and sheet2 has Custnumber