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

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

<tbody>
</tbody>

 

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