latest status with against dates

[vichuz]

Hi all,
Could you please help with my below query.

I am having a document register which contains transmittal number, document number submission date to the client, the status of the document, I need to find out the latest status of the document with latest date and approval code against each document number mentioned below. please check the link for more
details.

https://ibb.co/hbWw56

[TABLE="width: 888"]
<tbody>[TR]
[TD][TABLE="width: 888"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Transmittal
Reference[/TD]
[TD]FEES Document No.[/TD]
[TD]Document Title/Description[/TD]
[TD]Rev.[/TD]
[TD]Vendor Submission date[/TD]
[TD]Purpose of
Submission[/TD]
[TD]Client Response Date[/TD]
[TD]Review
Status[/TD]
[/TR]
[TR]
[TD]1TRN-611[/TD]
[TD]J---4005[/TD]
[TD]layout[/TD]
[TD]A[/TD]
[TD]26-Oct-16[/TD]
[TD]IFR[/TD]
[TD]9-Nov-16[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-689[/TD]
[TD]J---4005[/TD]
[TD]layout[/TD]
[TD]0[/TD]
[TD]17-Nov-16[/TD]
[TD]IFA[/TD]
[TD]4-Dec-16[/TD]
[TD]Code-2[/TD]
[/TR]
[TR]
[TD]1TRN-721[/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]A[/TD]
[TD]04-Dec-16[/TD]
[TD]IFR[/TD]
[TD]9-Jan-17[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-814[/TD]
[TD]J---4013[/TD]
[TD]EQUIPMENT DWGS[/TD]
[TD]A[/TD]
[TD]02-Feb-17[/TD]
[TD]IFR[/TD]
[TD]20-Feb-17[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-829[/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]B[/TD]
[TD]11-Feb-17[/TD]
[TD]IFA[/TD]
[TD]22-Feb-17[/TD]
[TD]code-3[/TD]
[/TR]
[TR]
[TD]1TRN-889[/TD]
[TD]J---4013[/TD]
[TD]EQUIPMENT DWGS[/TD]
[TD]B[/TD]
[TD]04-Mar-17[/TD]
[TD]IFA[/TD]
[TD]13-Mar-17[/TD]
[TD]Code-2[/TD]
[/TR]
[TR]
[TD]1TRN-891[/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]0[/TD]
[TD]05-Mar-17[/TD]
[TD]IFA[/TD]
[TD]13-Mar-17[/TD]
[TD]code-3[/TD]
[/TR]
[TR]
[TD]1TRN-1017[/TD]
[TD]J---4007[/TD]
[TD]Isometrics[/TD]
[TD]A[/TD]
[TD]04-Jun-17[/TD]
[TD]IFR[/TD]
[TD]29-Jun-17[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-1022[/TD]
[TD]J---4006[/TD]
[TD]Loop Drawings[/TD]
[TD]A[/TD]
[TD]08-Jun-17[/TD]
[TD]IFR[/TD]
[TD]11-Jul-17[/TD]
[TD]Code-2[/TD]
[/TR]
[TR]
[TD]1TRN-1027[/TD]
[TD]J---4005[/TD]
[TD]layout[/TD]
[TD]1[/TD]
[TD]14-Jun-17[/TD]
[TD]IFC[/TD]
[TD]3-Jul-17[/TD]
[TD]code-2[/TD]
[/TR]
[TR]
[TD]1TRN-1028[/TD]
[TD]J---4012[/TD]
[TD]INSTRUMENT DIAGRAM[/TD]
[TD]A[/TD]
[TD]14-Jun-17[/TD]
[TD]IFR[/TD]
[TD]4-Jul-17[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-1040[/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]1[/TD]
[TD]22-Jun-17[/TD]
[TD]IFC[/TD]
[TD]11-Jul-17[/TD]
[TD]code-2[/TD]
[/TR]
[TR]
[TD]1TRN-1041[/TD]
[TD]J---4013[/TD]
[TD]EQUIPMENT DWGS[/TD]
[TD]0[/TD]
[TD]22-Jun-17[/TD]
[TD]IFA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1TRN-1050[/TD]
[TD]J---4007[/TD]
[TD]Isometrics[/TD]
[TD]B[/TD]
[TD]05-Jul-17[/TD]
[TD]IFA[/TD]
[TD]26-Jul-17[/TD]
[TD]Code-3[/TD]
[/TR]
[TR]
[TD]1TRN-1052[/TD]
[TD]J---4012[/TD]
[TD]INSTRUMENT DIAGRAM[/TD]
[TD]0[/TD]
[TD]10-Jul-17[/TD]
[TD]IFA[/TD]
[TD]22-Jul-17[/TD]
[TD]code-2[/TD]
[/TR]
[TR]
[TD]1TRN-1055[/TD]
[TD]J---4006[/TD]
[TD]Loop Drawings[/TD]
[TD]0[/TD]
[TD]17-Jul-17[/TD]
[TD]IFA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1TRN-1065[/TD]
[TD]J---4012[/TD]
[TD]INSTRUMENT DIAGRAM[/TD]
[TD]1[/TD]
[TD]01-Aug-17[/TD]
[TD]IFC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1TRN-1065[/TD]
[TD]J---4007[/TD]
[TD]Isometrics[/TD]
[TD]C[/TD]
[TD]01-Aug-17[/TD]
[TD]IFA[/TD]
[TD]27-Aug-17[/TD]
[TD]Code-2[/TD]
[/TR]
[TR]
[TD]1TRN-1074[/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]2[/TD]
[TD]14-Aug-17[/TD]
[TD]IFC[/TD]
[TD]26-Sep-17[/TD]
[TD]code-1[/TD]
[/TR]
[TR]
[TD]1TRN-1074[/TD]
[TD]J---4005[/TD]
[TD]layout[/TD]
[TD]2[/TD]
[TD]14-Aug-17[/TD]
[TD]IFC[/TD]
[TD]26-Sep-17[/TD]
[TD]code-1[/TD]
[/TR]
[TR]
[TD]1TRN-1081[/TD]
[TD]J---4007[/TD]
[TD]Isometrics[/TD]
[TD]0[/TD]
[TD]28-Aug-17[/TD]
[TD]IFA[/TD]
[TD]26-Sep-17[/TD]
[TD]code-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Required answer[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Document number[/TD]
[TD]Document Title/Description[/TD]
[TD]Rev.[/TD]
[TD]Review
Status[/TD]
[TD]Current communication (pending)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4005[/TD]
[TD]layout[/TD]
[TD]3[/TD]
[TD]code-1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4006[/TD]
[TD]Loop Drawings[/TD]
[TD]0[/TD]
[TD]Code-2[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4007[/TD]
[TD]Isometrics[/TD]
[TD]0[/TD]
[TD]code-1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4009[/TD]
[TD]GA Drawings[/TD]
[TD]2[/TD]
[TD]code-1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4012[/TD]
[TD]INSTRUMENT DIAGRAM[/TD]
[TD]1[/TD]
[TD]code-2[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J---4013[/TD]
[TD]EQUIPMENT DWGS[/TD]
[TD]1[/TD]
[TD]code-2[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

Take a look at this:

Book1
	A	B	C	D	E
1	Document number	Document Title/Description	Rev.	Review Status	Current communication (pending)
2	J---4005	layout	2	code-1	Vendor
3	J---4006	Loop Drawings	0	Code-2	Client
4	J---4007	Isometrics	0	code-1	Vendor
5	J---4009	GA Drawings	2	code-1	Vendor
6	J---4012	INSTRUMENT DIAGRAM	1	code-2	Client
7	J---4013	EQUIPMENT DWGS	0	Code-2	Client
Sheet2

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet1!$B$2:$B$22, MATCH(SMALL(IF(COUNTIF($A$1:A1,Sheet1!$B$2:$B$22)=0, COUNTIF(Sheet1!$B$2:$B$22, "<"&Sheet1!$B$2:$B$22), ""), 1), COUNTIF(Sheet1!$B$2:$B$22, "<"&Sheet1!$B$2:$B$22), 0)),"")}
B2	{=IF(A2="","",INDEX(Sheet1!$C$2:$C$22,LARGE(IF(Sheet1!$B$2:$B$22=A2,ROW(Sheet1!$E$2:$E$22)-1,1),1)))}
C2	{=IF(A2="","",INDEX(Sheet1!$D$2:$D$22,LARGE(IF(Sheet1!$B$2:$B$22=A2,ROW(Sheet1!$E$2:$E$22)-1,0),1)))}
D2	{=IF(A2="","",INDEX(Sheet1!$H$2:$H$22,LARGE(IF(Sheet1!$B$2:$B$22=A2,ROW(Sheet1!$E$2:$E$22)-1,0),IF(E2="Vendor",1,2))))}
E2	{=IFERROR(LOOKUP(INDEX(Sheet1!$G$2:$G$22,LARGE(IF(Sheet1!$B$2:$B$22=A2,ROW(Sheet1!$E$2:$E$22)-1,""),1)),{0;1},{"Client";"Vendor"}),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.