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 insheetB
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 insheetA
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
Advertisement
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