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:
[TABLE="width: 530"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Part Number[/TD]
[TD] Drawing title[/TD]
[TD]No. of drwg.[/TD]
[TD]Drawing rev.[/TD]
[TD]Date of Distribution[/TD]
[/TR]
[TR]
[TD="align: right"]63550 [/TD]
[TD]RES4[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]18751 [/TD]
[TD]Led[/TD]
[TD]1[/TD]
[TD]F[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]21603 [/TD]
[TD]PCB ,1240[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Dec/26/2016[/TD]
[/TR]
[TR]
[TD="align: right"]22744[/TD]
[TD] ASSY,CPU[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Jun/26/2014[/TD]
[/TR]
[TR]
[TD="align: right"]22744[/TD]
[TD] PCB CPU[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]May/13/2014[/TD]
[/TR]
[TR]
[TD="align: right"]22745 [/TD]
[TD]BOARD,CPU[/TD]
[TD]9[/TD]
[TD]E[/TD]
[TD]Mar/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]26094 [/TD]
[TD]CABLE, 23[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]Jan/22/2018[/TD]
[/TR]
[TR]
[TD="align: right"]26094[/TD]
[TD] CABLE, 232C[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]Oct/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]26094[/TD]
[TD] CABLE, 232[/TD]
[TD]1[/TD]
[TD]B[/TD]
[TD]Feb/05/2016[/TD]
[/TR]
[TR]
[TD="align: right"]26094[/TD]
[TD] CABLE[/TD]
[TD]1[/TD]
[TD]B[/TD]
[TD]Feb/05/2016[/TD]
[/TR]
[TR]
[TD="align: right"]26251 [/TD]
[TD]RES[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Mar/13/2015[/TD]
[/TR]
[TR]
[TD="align: right"]53454 [/TD]
[TD]RES2[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Mar/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]55425 [/TD]
[TD]EMC AS[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]55458[/TD]
[TD] ASSY,12MOTHER[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD]Dec/26/2016[/TD]
[/TR]
[TR]
[TD="align: right"]55458[/TD]
[TD] ASSY,MOTHER[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD]Jun/02/2015[/TD]
[/TR]
</tbody>[/TABLE]

RESULT SHOULD BE LIKE THIS:
[TABLE="width: 302"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part Number[/TD]
[TD]Drawing rev.[/TD]
[TD]Date of Distribution[/TD]
[/TR]
[TR]
[TD="align: right"]63550 [/TD]
[TD]A[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]18751 [/TD]
[TD]F[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]21603 [/TD]
[TD]A[/TD]
[TD]Dec/26/2016[/TD]
[/TR]
[TR]
[TD="align: right"]22744[/TD]
[TD] A[/TD]
[TD]Jun/26/2014[/TD]
[/TR]
[TR]
[TD="align: right"]22745[/TD]
[TD] E[/TD]
[TD]Mar/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]26094[/TD]
[TD] C[/TD]
[TD]Jan/22/2018[/TD]
[/TR]
[TR]
[TD="align: right"]26251 [/TD]
[TD]A[/TD]
[TD]Mar/13/2015[/TD]
[/TR]
[TR]
[TD="align: right"]53454 [/TD]
[TD]A[/TD]
[TD]Mar/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]55425[/TD]
[TD] A[/TD]
[TD]Nov/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]55458 [/TD]
[TD]B[/TD]
[TD]Dec/26/2016[/TD]
[/TR]
</tbody>[/TABLE]

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.