Find out the Latest revision of the document

[sushilkadam]

Hi everyone, I need your expert advice for below
I have a sheet of all the document number listed in Column A, Title in Column B, and Revision in Column C

Document number will be same for all the revision coming
Hence I want to track the exact current revision of the document

Below is the sample table

Document Number	Description	Rev	Latest
JA-6611-10001-0000	Master Document Deliverables Register	01
JA-6611-10001-0000	Master Document Deliverables Register	02
JA-6611-10001-0000	Master Document Deliverables Register	03	L
AA-5880-10002-0000	Contractor Key Personnel Mobilisation Plan	01
AA-5880-10002-0000	Contractor Key Personnel Mobilisation Plan	02
AA-5880-10002-0000	Contractor Key Personnel Mobilisation Plan	03	L
AA-5880-10003-0000	Contractor Mobilization Plan	01
AA-5880-10003-0000	Contractor Mobilization Plan	02	L
HX-5798-10004-0000	Contract HSE Plan	01
HX-5798-10004-0000	Contract HSE Plan	02
HX-5798-10004-0000	Contract HSE Plan	03
HX-5798-10004-0000	Contract HSE Plan	04	L
CX-4180-10005-0001	Site Camp Layout	01
CX-4180-10005-0001	Site Camp Layout	02
CX-4180-10005-0001	Site Camp Layout	03
CX-4180-10005-0001	Site Camp Layout	04
CX-4180-10005-0001	Site Camp Layout	05	L

<tbody>
</tbody>

Please help

 

[VBA Geek]

this in d2

=IF(COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,B2)=$C2,"L","")

 

[AlphaFrog]

If the revisions are sequential starting with 1, you could use a COUNTIF function to get the count of a specific document number.

=COUNTIF(A:A, Document_Number)


If the revisions are not sequential, use MAX(IF function to get the largest revision number for a given document number

This is an array formula. Confirm with Ctrl+Shift+Enter
=MAX(IF(A1:A30=Doc_Number, C1:C30,0))

 

[sushilkadam]

Thanks