lookup with multiple criteria for the last status

  • hi Jo,

    i have updated inspection record in sheet 'record' as follow

    A1="Record"

    A2="Date", B2="Insp", C2="SN", D2="Result"

    A3="11-Jan", B3="PTAM", C3="CT-01", D3="ACC"

    A4="11-Jan", B4="FNL", C4="CT-01", D4="REJ"

    A5="11-Jan", B5="PTAM", C5="CT-02", D5="ACC"

    A6="12-Jan", B6="FNL", C6="CT-02", D6="ACC"

    A7="12-Jan", B7="FNL", C7="CT-01", D7="ACC"

    A8="12-Jan", B8="PTAM", C8="CT-03", D8="ACC"

    A9="13-Jan", B9="FNL", C9="CT-03", D9="ACC"

    A10="13-Jan", B10="PTAM", C10="CT-04", D10="REJ"

    A11="13-Jan", B11="FNL", C11="CT-01", D11="ACC"

    A12="14-Jan", B12="PTAM", C12="CT-04", D12="ACC"

    A13="14-Jan", B13="FNL", C13="CT-04", D13="REJ"


    Note: there are only 2 'Inspection Type' (PTAM and FNL) and 2 types of 'results (ACC and REJ)


    i want to summarize the latest status of each 'SN' in sheet "summary"

    A1="Summary"

    A2="SN", B2="PTAM", E2="FNL"

    A3="", B3="Date", C3="Final Result", D3="Remark", E3="Date", F3="Final Result", G3="Length", H3="Remark"

    A4="CT-01" , A5="CT-02" , A6="CT-03" , A7="CT-04"


    B4:B7 I want to get the latest 'Date' of inspection (PTAM)

    C4:C7 I want to get the latest 'Result' of inspection (PTAM)

    D4:D7 I want to get the latest 'Remark' of inspection (PTAM)

    E4:E7 I want to get the latest 'Date' of inspection (FNL)

    F4:F7 I want to get the latest 'Result' of inspection (FNL)

    G4:G7 I want to get the latest 'Length' of inspection (FNL)

    H4:H7 I want to get the latest 'Remark' of inspection (FNL)


    I want to get the below data as follow

    B4="11-Jan", C4="ACC", D4="", E4="13-Jan", F4="ACC", G4="800", H4="re-inspect"

    B5="11-Jan", C5="ACC", D5="", E5="12-Jan", F5="ACC", G5="1000", H5=""

    B6="12-Jan", C6="ACC", D6="", E6="13-Jan", F6="ACC", G6="900", H6=""

    B7="14-Jan", C7="ACC", D7="", E7="14-Jan", F7="REJ", G7="1000", H7="3 loc"

  • Hello,


    No need for a macro ...


    You are saying multiple criteria ... but it seems that the last instance of the Serial Number is enough ...


    Following array formula returns the row number of your Last Instance

    Code
    1. =MAX(IF(record!$C$3:$C$13=A4,ROW(record!$A$3:$A$13)-MIN(ROW(record!$A$3:$A$13))+1))+2


    Attached is your test file


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • Hi Carim,

    I actually already settled with excel formula but the my data actually very big (SN about 10000 rows and inspection type about 25 column) and linked to 4 sheets, it cause my computer running very slowly.


    I am thinking that macro will help me

  • Hi Carim,

    I actually already settled with excel formula but the my data actually very big (SN about 10000 rows and inspection type about 25 column) and linked to 4 sheets, it cause my computer running very slowly.


    I am thinking that macro will help me

    Re,


    Why haven't you mentioned this in your first message ?


    What is formula you are already using ?


    Why is your formula not included in the test file you have attached ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Once you have tested the macro ( see message # 7 ) ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • royUK

    Changed the title of the thread from “Q : lookup with multiple criteria for the last status” to “lookup with multiple criteria for the last status”.