Get the latest date of drawing and its revision number

[Stacy Rueda]

Hi Guys,

Have a good day.

I have excel file with duplicated part numbers per cell but these part numbers have different revision letter and dates. Now, my problem is, i want to get the unique part number and its latest revision letter and date by using Excel formula. Please help me coz the one that i am using has error. Please see below table
INDEX($E$2:$E$11990,MAX(IF($B$2:$B$11990=$I6,ROW($B$2:$B$11990)-ROW($B$2)+1)))


BEFORE:

Part Number	Drawing title	No. of drwg.	Drawing rev.	Date of Distribution
63550	RES4	1	A	Nov/13/2013
18751	Led	1	F	Nov/13/2013
21603	PCB ,1240	1	A	Dec/26/2016
22744	ASSY,CPU	1	A	Jun/26/2014
22744	PCB CPU	1	A	May/13/2014
22745	BOARD,CPU	9	E	Mar/02/2017
26094	CABLE, 23	1	C	Jan/22/2018
26094	CABLE, 232C	1	C	Oct/03/2017
26094	CABLE, 232	1	B	Feb/05/2016
26094	CABLE	1	B	Feb/05/2016
26251	RES	1	A	Mar/13/2015
53454	RES2	1	A	Mar/02/2017
55425	EMC AS	1	A	Nov/13/2013
55458	ASSY,12MOTHER	2	B	Dec/26/2016
55458	ASSY,MOTHER	2	B	Jun/02/2015

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

RESULT SHOULD BE LIKE THIS:

Part Number	Drawing rev.	Date of Distribution
63550	A	Nov/13/2013
18751	F	Nov/13/2013
21603	A	Dec/26/2016
22744	A	Jun/26/2014
22745	E	Mar/02/2017
26094	C	Jan/22/2018
26251	A	Mar/13/2015
53454	A	Mar/02/2017
55425	A	Nov/13/2013
55458	B	Dec/26/2016

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I have edited the table for this post, cell number may differ in the table above.

Thanks a lot!

 

[steve the fish]

Are the dates true dates? That will be necessary.

 

[Stacy Rueda]

hi steve the fish, yes that's true, because we have monitoring of drawing that we recieved. the latest date every part number and revision letter is what i need to reflect on formula i am using.

Thanks a lot

 

[Stacy Rueda]

hi @mr marcelo, i know u are excel genius, can u help me with this? appreciate ur reply. thanks a lot.