Check the data from two sheet and result on third sheet

[shiven_k]

Hi Forum,

Need your help, I am stuck to one problem in Excel.

Problem:

I have excel file, consists of 4 sheets Data, PF, Gratuity, Discrepancy. Here I have Emp no. field as key field, now here first I have to check Data Sheet data with PF.

For example,

Suppose, we are considering Emp no.: 4 from data sheet. It will check in PF sheet for same Emp no. if it find then it will check the relationship and first name and last name from data sheet. If it is ok then it will return output in discrepancy sheet with OK. Discrepancy sheet format:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Emp No.[/TD]
[TD]Spouse [/TD]
[TD]Child1 [/TD]
[TD]Child2 [/TD]
[TD]Father [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] OK[/TD]
[TD]OK [/TD]
[TD]OK [/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

(All the relationship present in Data Sheet in Column)

Sample Data:

Data Sheet

[TABLE="width: 1029"]
<tbody>[TR]
[TD]Emp No.[/TD]
[TD]Name of Employee or Applicant[/TD]
[TD]Family relationship[/TD]
[TD]First name[/TD]
[TD]Last name[/TD]
[TD]Date of Birth[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BHATTACHARYA SUBRATA[/TD]
[TD]Spouse[/TD]
[TD]SUBARNA[/TD]
[TD]BHATTACHARYA[/TD]
[TD]13.11.1973[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BHATTACHARYA SUBRATA[/TD]
[TD]Child[/TD]
[TD]MANJIMA[/TD]
[TD]BHATTACHARYA[/TD]
[TD]09.07.2003[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BHATTACHARYA SUBRATA[/TD]
[TD]Father / Mother[/TD]
[TD]HARI NARAYAN[/TD]
[TD]BHATTACHARYA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MOHIT SK. ABDUL[/TD]
[TD]Spouse[/TD]
[TD]MEHBUBA[/TD]
[TD]BEGUM[/TD]
[TD]10.10.1973[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MOHIT SK. ABDUL[/TD]
[TD]Child[/TD]
[TD]SHIRIN[/TD]
[TD]SULTANA[/TD]
[TD]28.02.1995[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MOHIT SK. ABDUL[/TD]
[TD]Child[/TD]
[TD]SK.[/TD]
[TD]SAHIL[/TD]
[TD]16.04.1998[/TD]
[/TR]
</tbody>[/TABLE]

PF Sheet
[TABLE="width: 784"]
<tbody>[TR]
[TD]Emp. No.[/TD]
[TD]Sub Type[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]DOB[/TD]
[TD]%[/TD]
[TD]Sex (M/F)[/TD]
[TD]Relation[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]EPF[/TD]
[TD]SUBARNA[/TD]
[TD]BHATTACHARYA[/TD]
[TD]13.11.1973[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]EPF[/TD]
[TD]MEHBUBA[/TD]
[TD]BEGUM[/TD]
[TD]10.10.1973[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]EPF[/TD]
[TD]SHIRIN[/TD]
[TD]SULTANA[/TD]
[TD]28.02.1995[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]DAUGHTER[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]EPF[/TD]
[TD]SK MAHMOOD[/TD]
[TD]ELAHI[/TD]
[TD]16.04.1956[/TD]
[TD="align: right"]100[/TD]
[TD]M[/TD]
[TD]SON[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]EPF[/TD]
[TD]SOMALI[/TD]
[TD]SARKAR[/TD]
[TD]09.11.1979[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]EPF[/TD]
[TD]MOUSUMI[/TD]
[TD]SENGUPTA[/TD]
[TD]31.08.1967[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]EPF[/TD]
[TD]ALO[/TD]
[TD]DUTTA[/TD]
[TD]01.09.1968[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]EPF[/TD]
[TD]SUCHISMITA[/TD]
[TD]NAG[/TD]
[TD]21.03.1964[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]EPF[/TD]
[TD]MADHURI[/TD]
[TD]SINGH[/TD]
[TD]01.03.1976[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]EPF[/TD]
[TD]ANIKET[/TD]
[TD]SINGH[/TD]
[TD]03.09.2001[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]SON[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]EPF[/TD]
[TD]EDITH[/TD]
[TD]D'SA[/TD]
[TD]29.09.1971[/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]EPF[/TD]
[TD]N.[/TD]
[TD]SHYAMALA[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD]F[/TD]
[TD]SPOUSE[/TD]
[/TR]
</tbody>[/TABLE]

Result Sheet

[TABLE="width: 338"]
<tbody>[TR]
[TD]E.Code[/TD]
[TD]PF_Spouse[/TD]
[TD]PF_Child1[/TD]
[TD]PF_Child2[/TD]
[TD]PF_Parent[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]OK[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

And so on.

Please help,
Thanks,
Shivendra