i need help to have coding (macro excel) to lookup with multiple criteria for the last status.
i've attached file
thanks
i need help to have coding (macro excel) to lookup with multiple criteria for the last status.
i've attached file
thanks
Can't you explain what you want to achieve?
A lot of people don't like opening strangers workbooks/files.
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
Attached is your test file
Hope this will help
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 ?
Attached is Version 2 with your macro
carim,
sorry for not mentioning in my first message
i use array formula index & match
Once you have tested the macro ( see message # 7 ) ... feel free to share your comments
Thanks for the Like
Don’t have an account yet? Register yourself now and be a part of our community!