Display values based on matching column data

[sarat]

Hello,

From the attached sheet, I want to insert one column automatically in Emp sheet, rename as 'Merge_status' after data end and populate the value as 1,2 and 0 based on matching between two sheets emp and empmaster.

Grouping of data should be based on first column.


Note that Empdept is present in more than one country (i.e. 102 for UK and Japan). Therefore matching should be considered as UK-Rakesh, UK-Nilesh, UK-Kikesh, Japan-Rakesh, Japan-Nilesh, Japan-Kikesh. Example as row number 6


1)Merge_status will be 1 when any of the country (may be more than), ename and job column from Emp sheet is matching with country, ename and job column of EmpMaster sheet.
e.g.
For row number 2 where data (US-Manish-Clerk) is matching with data (US-ManishKumar-Clerk)--> Merge_status is 1
For row number 3 where data (US-Kiran Kumar-Clerk) is matching with data (US-Kiran-Clerk)--> Merge_status is 1
For row number 6 where data (Japan-Rakesh-Manager) is matching with data (Japan-Rakesh-Manager)--> Merge_status is 1 as different emp with same name may work in UK and Japan.


2)Merge_status will be 2 when any of the country (may be more than) and ename column from Emp sheet is matching with country and ename column of EmpMaster sheet where as job column data is blank
e.g.
For row number 4 where data (US-Harish) from emp sheet is matching with data (US-Harish) from empmaster sheet but job field is blank in emp sheet --> Merge_status is 2
For row number 8 where data (Japan-Kikesh) from emp sheet is matching with data (Japan-Kikesh) from empmaster sheet but job field is blank in emp sheet --> Merge_status is 2


3)Other condition should be considered as zero as merge_status.


eg
For row number 5, even though data (US-Nagesh) from emp sheet is matching with data (US-Nagesh) from empmaster sheet but job field was not matching between two sheet --> Merge_status is 0
For row number 7, even though data (UK-Nilesh) from emp sheet is matching with data (UK-Nilesh) from empmaster sheet but job field was not matching between two sheet --> Merge_status is 0