Drawing register- Formula to find latest revision, return code etc

Thank you Gregb11 for the quick response.

I tried the formula in the complete log (65000 rows) then it was showing some blank columns.
I split the log discipline/ type wise and i applied the formula then it shows all columns filled but latest revision is not taking sequence wise. (it is taking revision A,BC..)

The revision sequence is A,B,C,D,.....1,2,3,4,....AB,AB1,AB2,AB3,AB4,.....ZO,Z1,Z2,Z3,Z4,....

is it possible to get the latest revision as per the sequence.

attached screenshot

thanks in advance

Did you already think about a VBA (macro) solution.
That can be much faster then array-formulas.
It needs more time for programming and it requires some understanding of VBA for maintaining and upgrading the tool.
So it is up to you if you want to give it a try.
Maybe you can explain a bit more about document control.
I know from experience that it needs a extended query tool to be able to answer varies questions.

Hi Piet,

I am not good at VBA but I know how to add code in excel.
It would be much appreciated if you could try.

I will explain about the register. I have 2 separate reports, incoming and outgoing, which is generated from the system.
in the incoming report, I have Transmittal no, Document no, Revision, Title, Date Etc
in the outgoing report, I have RTS No (Outgoing transmittal to the contractor), Return code (i.e approval code for that document) Etc.

I have to combine these two reports to get a log register. i.e get the RTS no for the corresponding transmittal no and the Return code for that particular document from the outgoing report.

What am looking for is a report which has all the relevant information like Transmittal no., Document No. Title, Revision, RTS No, Return code, current revision (latest revision).

also am looking for a separate report which shows the pending approvals (i.e the incoming transmittals which don't have RTS number).

The revision sequence of the drawing starts with A, B, C, D......0,1, 2, 3, 4.....AB, AB1, AB2, AB3, AB4....Z0, Z1, Z2, Z3, Z4.....

the first revision is always A (alphabetical order) when the document gets approved next revision should be '0'. after that will be 1,2,3...(numerical order). When the document is asbuilt stage revision starts with AB (AB1, AB2, AB3...etc) or Z0 (Z1, Z2, 3...etc). i.e all the engineering documents come under AB revisions. All the vendor documents come under Z0 revisions.

Hope it is clear. if you need any further information or the sample files i will provide you.

Thank you

I gave it a try.
Incoming and outgoing reports are now combined in 1 workbook
After a new update you need to click the button: Generate Document Index on sheet INC_index
on the last 2 sheets you can run queries on Incoming and outgoing documents
It is a begining......

H Piet,

I appreciate your help.

INC_index sheet is fine. Latest revision, latest date for that revision & number of revisions is perfectly alright.

is it possible to add two more columns:
1 Return code (against the latest revision)
2. Contractor_Trans_Number for the document number.
-------------------------------------------------------------------
in the Search_Outgoing_TM tab

Your 'C.T.N' query is against the Contractor_Trans_Number has some issues.

For e.g CTN query for '0005' returns code as 'C' and Latest revision as 'B' which is not accurate for that document (001700-0000-CB30-SHD-0002. The same document is repeated two times with A,B revisions (return code C ,B) in different transmittals.

The revisions and return code is based on document number hence the query should be against the Document number.

Search incoming_TM also the same.

Thanks

I am not sure if i understand it.
Here is my 2nd version.
I hope a step in the right direction.

For every unique document on 1 line you can have more than 1 outgoing submissions.
To keep the Document Index singel lines, I put all the outgoing submissions in 1 cell by using linefeeds after every action.
I hope this is what you were looking for and that you learned something more about Excel and VBA.

Because this is a forum where people learn from helpers.
Did you learn from my ansers and did you try to solve the last question yourself ?
Where stops your knowledge of Excel, so we can work to the solution from that point where you will be in the lead.
Hope you understand my point.

I am glad that you tried to find a solution yourself.
It is a smart workaround
Because it needs some manual copying /pasting data, I extended the VBA code so its going automatically.

Added some VBA code to flag the outstanding RTS's
If I understood the proces.
Does RTS mean: Return To Sender ?
And IFI means: Issue For Information only ?
What stand the Return Codes A, B, C, D,..... for ?

I will study the process and come back when I have the solution.

I had an other look at it and came to the conclusion that a Document History would help to be able to run usefull queries.
In sheet Doc_Cycle you will find tool to generate a history record per document.
on this table you can filter: on a document, or documents with no incomings or no outgoings, etc,
Hope this will solve a lot of questions.....

The counting is done by the VBA code
Now you can filter on Doc.Type and/or Discipline
Column AZ now shows Action to submit RTS

I changed the VBA code and now it looks like it is working.....

your welcome

Hi Rhommel,
Do you mean IFC - Issued for Comments, IFR - Issued for Review, IFA - Issued for Approval ?
How should the datasets look like ?
adding a sheet click on + sign on worksheet bar
Add_Worksheet.jpg

Hi Piet,

It would be great help if you can add VBA code for IFC,IFA,IFR in Excel Attach? IFI works perfectly there.

Thanks,
Rhommel

Ok Rhommel,
I think I understand what you are trying to achieve.
I am quitte busy at the moment.
So I will give it a try coming week.

Hi Piet,

I hope you can fix that today if you have a free time so i can use it at my work. Your immediate response will be highly appreciated.

thanks,
Rhommel

Originally Posted by rhommel

Hi Piet,

I hope you can fix that today if you have a free time so i can use it at my work. Your immediate response will be highly appreciated.

thanks,
Rhommel

1. do not demand fast replies from members, we all volunteer here and give time and help as and when we can

2. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

thanks for your support, Ford.
I will wait for Rhommel to start his new topic.
How will I know when he did ?
Through a PM I assume.

Piet - of course

They could PM you, or, because they only have a few posts, you could check in their profile and look for Latest Posts.