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

[TABLE="width: 553"]
<tbody>[TR]
[TD]Document Number
[/TD]
[TD]Description
[/TD]
[TD]Rev
[/TD]
[TD]Latest
[/TD]
[/TR]
[TR]
[TD]JA-6611-10001-0000
[/TD]
[TD]Master Document Deliverables Register
[/TD]
[TD]01
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]JA-6611-10001-0000
[/TD]
[TD]Master Document Deliverables Register
[/TD]
[TD]02
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]JA-6611-10001-0000
[/TD]
[TD]Master Document Deliverables Register
[/TD]
[TD]03
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]AA-5880-10002-0000
[/TD]
[TD]Contractor Key Personnel Mobilisation Plan
[/TD]
[TD]01
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AA-5880-10002-0000
[/TD]
[TD]Contractor Key Personnel Mobilisation Plan
[/TD]
[TD]02
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AA-5880-10002-0000
[/TD]
[TD]Contractor Key Personnel Mobilisation Plan
[/TD]
[TD]03
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]AA-5880-10003-0000
[/TD]
[TD]Contractor Mobilization Plan
[/TD]
[TD]01
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AA-5880-10003-0000
[/TD]
[TD]Contractor Mobilization Plan
[/TD]
[TD]02
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]HX-5798-10004-0000
[/TD]
[TD]Contract HSE Plan
[/TD]
[TD]01
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]HX-5798-10004-0000
[/TD]
[TD]Contract HSE Plan
[/TD]
[TD]02
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]HX-5798-10004-0000
[/TD]
[TD]Contract HSE Plan
[/TD]
[TD]03
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]HX-5798-10004-0000
[/TD]
[TD]Contract HSE Plan
[/TD]
[TD]04
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]CX-4180-10005-0001
[/TD]
[TD]Site Camp Layout
[/TD]
[TD]01
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CX-4180-10005-0001
[/TD]
[TD]Site Camp Layout
[/TD]
[TD]02
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CX-4180-10005-0001
[/TD]
[TD]Site Camp Layout
[/TD]
[TD]03
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CX-4180-10005-0001
[/TD]
[TD]Site Camp Layout
[/TD]
[TD]04
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CX-4180-10005-0001
[/TD]
[TD]Site Camp Layout
[/TD]
[TD]05
[/TD]
[TD]L
[/TD]
[/TR]
</tbody>[/TABLE]

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