formula for finding drawings' latest revision

[Baggeepants]

Below is an extract from a drawing register spreadsheet & in column B, I require a formula that displays the latest revision of the document. Column E onwards display the received/issued revisions of the documents listed in column A at different times during the life of the project. As new revisions are received a new column is inserted before column E & different suppliers use different revision codes. C1 being a later revision than P3.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Document Number

Current Revision

Reason for Issue​

R

R

R

ACT

APP

APP

ACT

R

R

APP

R

ACT

2

Title

4

C891-B2-AB-34391

Interlocking Control System Cable Block Diagram

E

A

5

C891-B2-AU-30155

SLUDGE & LAUNDER COLLECTION ACCESS ROAD LIGHTING GENERAL ARRANGEMENT

P2

6

C891-B2-AU-30159

PRIMARY DIGESTER TANK NO.4 HEAT EXCHANGER ACCESS WALKWAY LIGHTING GENERAL ARRANGEMENT

P2

7

C891-B2-AU-30161

WASHWATER PUMP WWS03P_01 BLOCK CABLE DIAGRAM

C2

8

C891-B2-AU-30180

WASHWATER PUMP WWS03P_01 CONNECTION DIAGRAM

C2

9

C891-B2-AU-30182

PST5 RIO BLOCK CABLE DIAGRAM

10

C891-B2-AU-30195

INLET & SLUDGE IMCC BLOCK CABLE DIAGRAM SHEET 4

P2

C2

11

C891-B2-AU-30200

INLET & SLUDGE MCC ICA COMPARTMENT A1 CONNECTION DIAGRAM SHEET 1 OF 5

C3

C2

C1

24

C891-HOGSS1ZZ-ELE-SPEC-10085-Addendum02

ICS Cable Schedule

5

1

25

Appendix 1

HMI Screen Switchboards

-

26

3334787_B03_04_A03 3 x CRE 45-2-1 VSD Booster Pump Data Sheets

VSD Booster Pump Data Sheets

-

27

99195220 as built

Hydro MPC Control Panel 2 Pump Pressure Boosting System EF

B

28

Control MPC

Installation & Operating Instructions

-

29

Hydro MPC

Service Instructions

-

<tbody>
</tbody>

 

[jorismoerings]

Hi,

Welcome to MrExcel,

Check if this works for you:

Book1
	A	B
1	Document Number	Current Revision
2	Title
3	C891-B2-AB-34391	E
4	C891-B2-AU-30155	P2
5	C891-B2-AU-30159	P2
6	C891-B2-AU-30161	C2
7	C891-B2-AU-30180	C2
8	C891-B2-AU-30182
9	C891-B2-AU-30195	P2
10	C891-B2-AU-30200	C3
11	C891-HOGSS1ZZ-ELE-SPEC-10085-Addendum02	5
12	Appendix 1	-
13	3334787_B03_04_A03 3 x CRE 45-2-1 VSD Booster Pump Data Sheets	-
14	99195220 as built	B
15	Control MPC	-
16	Hydro MPC	-
Sheet1

Cell Formulas
Range	Formula
B3	{=IFERROR(INDEX(E3:P3,MATCH(TRUE,E3:P3<>"",0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Baggeepants]

Hi,

Welcome to MrExcel,

Check if this works for you:

	A	B
1	Document Number	Current Revision
2	Title
3	C891-B2-AB-34391	E
4	C891-B2-AU-30155	P2
5	C891-B2-AU-30159	P2
6	C891-B2-AU-30161	C2
7	C891-B2-AU-30180	C2
8	C891-B2-AU-30182
9	C891-B2-AU-30195	P2
10	C891-B2-AU-30200	C3
11	C891-HOGSS1ZZ-ELE-SPEC-10085-Addendum02	5
12	Appendix 1	-
13	3334787_B03_04_A03 3 x CRE 45-2-1 VSD Booster Pump Data Sheets	-
14	99195220 as built	B
15	Control MPC	-
16	Hydro MPC	-

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

Sheet1​

Array Formulas Cell Formula B3 {=IFERROR(INDEX(E3:P3,MATCH(TRUE,E3:P3<>"",0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks, formula works a treat for most of the documents listed. But some of the drawings have revisions P1, P2 P3 which are provisional drawings, then the same drawing will have C1, C2 etc which are for construction finally followed by X which is 'As Built' (The quirks of the construction industry). Is there any way the formula could be adjusted to show the correct revision for these types of documents.

 

[jorismoerings]

Hi,

My formula was build upon the requirements of the latest revision was entered in a new inserted column. But clearly there's some hierarchy level within.
Could you explain the hierarchy level used with an example. EG if you look at line 10 the last code used is P2 directly followed by C2.
Does X always prevales over Cx codes and do Cx codes prevale over Px codes? What does an E code mean?
etc.

 

[Baggeepants]

Hi,

My formula was build upon the requirements of the latest revision was entered in a new inserted column. But clearly there's some hierarchy level within.
Could you explain the hierarchy level used with an example. EG if you look at line 10 the last code used is P2 directly followed by C2.
Does X always prevales over Cx codes and do Cx codes prevale over Px codes? What does an E code mean?
etc.

Within the construction industry, some companies use an alphabetical revision system (A, B, C etc) while others use an Alpha-Numeric system (P1, P2, C1, C2 etc). Where the P revisions are preliminary drawings, the C revisions are the construction drawings and the X revisions are the final drawings of what has been built/installed.

Here is an extract from a document register but as it is a current project there are as yet no X revision drawings.

Document Number

Current Revision

Reason for Issue

Title (Duplicate Document No.) (Superseded Document)

C891-B2-AU-30034

PFT3 & DIG4 PACKAGE PLANT WIRING BLOCK CABLE DIAGRAM

C1

C891-B2-AU-30047

PICKET FENCE THICKENER NO.3 ACCESS WALKWAYS LIGHTING GENERAL ARRANGEMENT

C4

C3

C2

C1

C891-B2-AU-30049

SLUDGE BLENDING TANK NO.2 PLATFORM LIGHTING GENERAL ARRANGEMENT

C4

C2

C1

C891-B2-AU-30055

PRIMARY DIGESTER TANK NO.4 ACCESS PLATFORM LIGHTING GENERAL ARRANGEMENT

C5

C2

C1

C891-B2-AU-30086

INLET & SLUDGE iMCC RTU10 BLOCK CABLE DIAGRAM SHEET 2 OF 3

C5

C4

C3

C1

C891-B2-AU-30126

PFT 3 DRIVE PUMP CONNECTION DIAGRAM

C1

C891-B2-AU-30127

DIGESTER 4 GRAVITY FLOW MACERATOR CONNECTION DIAGRAM

C1

C891-B2-AU-30128

PFT 3 DESLUDGE MOTOR AND PUMP CONNECTION DIAGRAM

C1

C891-B2-AU-30134

PFT NO.3 DESLUDGE PUMP PLINTH TASK & EMERGENCY LIGHTING GENERAL ARRANGEMENT

C2

C1

P1

C891-B2-AU-30155

SLUDGE & LAUNDER COLLECTION ACCESS ROAD LIGHTING GENERAL ARRANGEMENT

P2

P1

C891-B2-AU-30159

PRIMARY DIGESTER TANK NO.4 HEAT EXCHANGER ACCESS WALKWAY LIGHTING GENERAL ARRANGEMENT

P2

P1

C891-B2-AU-30195

INLET & SLUDGE IMCC BLOCK CABLE DIAGRAM SHEET 4

P2

C2

C1

C891-B2-AU-30200

INLET & SLUDGE MCC ICA COMPARTMENT A1 CONNECTION DIAGRAM SHEET 1 OF 5

C3

C2

C1

P1

C891-B2-AU-30209

110V INSTRUMENT DB WAYS 16-23 CONNECTION DIAGRAM

C2

C1

P1

C891-B2-AU-30212

INLET & SLUDGE MCC ICA COMPARTMENT A1 CONNECTION DIAGRAM SHEET 6 OF 6

C2

C1

P1

C891-B2-AU-30221

PST5 TANK, KIOSK & FERRIC PLANT FOOTPATH LIGHTING GA

P1

C891-DOC-B2-AU-30008

PICKET FENCE THICKENER NO.3 LIGHTING CALCULATION

C2

C1

P2

C891-DOC-B2-AU-30033

C1

C891-DOC-B2-AU-30097

110V INSTRUMENTATION DISTRIBUTION BOARD CABLE SCHEDULE

C4

C3

C1

P1

C891-DOC-B2-AU-30133

PFT3 DESLUDGE PUMPS PLINTH LIGHTING CALCULATION

C2

C1

P1

C891-DOC-B2-AU-30158

PRIMARY DIGESTER TANK NO.4 HEAT EXCHANGER ACCESS WALKWAY LIGHTING CALCULATION

P2

P1

C891-DOC-B2-AU-30220

PRIMARY DIGESTER TANK NO.5 FOOTPATHS LIGHTING CALCULATIONS

P1

<tbody>
</tbody>