Check the data from two sheet and result on third sheet

  • 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="class: cms_table, width: 500"]

    [tr][td]

    Emp No.

    [/td][/tr][tr][td][/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]

    [/TABLE](All the relationship present in Data Sheet in Column)Sample Data:Data Sheet[TABLE="class: cms_table, width: 1029"]

    [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]

    [/TABLE]PF Sheet [TABLE="class: cms_table, width: 784"]

    [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]

    [/TABLE]Result Sheet[TABLE="class: cms_table, width: 338"]

    [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]

    [/TABLE]And so on.Please help,Thanks,Shivendra

  • Re: Check the data from two sheet and result on third sheet


    Since the same employee numbers may appear multiple times in both sheets, it is more difficult to compare.


    I would suggest you use the PF sheet and add a "helper" column there first.


    In PF sheet, J2 add formula:


    [COLOR="#0000FF"]=IF(COUNTIFS(Data!A:A,A2,Data!C:C,H2,Data!D:D,TRIM(C2),Data!E:E,D2)>0,H2,"")
    [/COLOR]
    copied down.


    Notice, i used TRIM(C2) because the names in column C have a trailing space. You should really remove those, then use only C2 in the formula. This forces you to have clean data.


    Then in the Discrepance sheet, B2 use:


    [COLOR="#0000FF"]=IF(COUNTIFS(PF!A:A,A2,PF!J:J,MID(B$1,4,255)),"Ok","NA")[/COLOR]


    copied across and down.



    EDIT:


    If you can make so you only match the relationship, then you can avoid the helper column and use just this formula in your discrepancy sheet:


    [COLOR="#0000FF"]=IF(AND(COUNTIFS(Data!$A:$A,$A2,Data!$C:$C,MID(B$1,4,255))>0,COUNTIFS(PF!$A:$A,$A2,PF!$H:$H,MID(B$1,4,255))>0),"Ok","NA")[/COLOR]


    copied down and across

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!