• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find MIN and MAX of numbers and text

M

matt-gilbert

Member
Hi all,
I am trying to track revisions of drawings. Drawings have text revisions when they are not approved for construction and then revert to numbers when approved for construction. For example, in column A, Drawing No's are as follows with Revision No shown after the underscore;
Drwg No Min Drwg No Max Drwg No
123456_A 123456_A 123456_1
123456_B 123456_A 123456_1
123456_0 123456_A 123456_1
123456_1 123456_A 123456_1
AAAZZZ_C AAAZZZ_C AAAZZZ_1
AAAZZZ_0 AAAZZZ_C AAAZZZ_1
AAAZZZ_1 AAAZZZ_C AAAZZZ_1
I need formulas in columns B and C to return the minimum and maximum Drawing No's as shown above. Ideally, I would like the formulas to look at entire column (e.g. A:A) but disregard header row (row 1). Thanks in advance. Matt
 
Hi all (again),
Another formula I would like (in Column D) is to identify the previous Drawing No Revision No i.e. row 2 would not return anything, but row 3 would return "123456_A", row 4 "123456_B", row 5 "123456_0" etc. Forgot to mention that Drawing No's and Revision No's can be various lengths and not fixed as shown above. Thanks (again!). Matt
 
Hi Matt ,

A few clarifications :

1. Will the revision suffix ( whether alpha or numeric ) always be separated from the main drawing number by an underscore ?

2. Will there ever be two or more underscores in a full drawing ID ?

3. Will the revision suffix ever be more than 1 character if alpha ? What about when it is a number ?

4. Will the sorting always be alpha before number ? Or is it that which ever appears first is the earlier revision suffix , and which ever appears later is the later revision suffix ?

Narayan
 
Hi Narayan,
In answer to your questions;
1. Yes. I have chosen the underscore as the delimiter. It could be anything else i.e. / - ^ etc
2. There could be. It would depend on the author. These could be removed and/or replaced if need be. A common Drawing No example would be "7482-035-EL-006_0"
3. Revision suffix could be AA or 01. I wouldn't expect it to be any more than 2 characters.
4. If I understand your question correctly, yes. As I mentioned in my original post, Drawing No's have alpha Revision No and then revert to number. In my experience, I have found this to be the case in 99% of circumstances (in Australia) but it could happen I guess.
I hope my answers help. Thanks. Matt
 
Hi Narayan,

Thank you for your prompt response.

I think I know what you meant by your Q4 earlier. I have tested your formulas and they work while the data in column A is in ascending order i.e. alpha and then number. The data I have needs to be sorted frequently (by other data not mentioned in this post) and therefore column A data will be all over the place. The amount of data will also be quite large (1000+ rows).

Sorry I didn't mention this earlier.

Matt
 
Hi Narayan,

Perfect! This works great, thank you so much.

Is there a way to identify the Previous Revision as per Column F on attached?

Matt
 

Attachments

  • Matt_Example-2.xlsx
    9.9 KB · Views: 2
Hi Narayan,

It looks like your formula is returning the Previous Revision of the Maximum Revision (Column G). What I am looking for is the Previous Revision of the Drwg No (Column A). Please refer file I previously uploaded.

Thanks

Matt
 
Hi Narayan,

Excellent work! You are a true Excel Ninja! :)

Thank you so much for your help. I might have some more queries as this is only the beginning of a very complex spreadsheet...

Thanks again

Matt
 
Hi Narayan,

Is there a way to create a Sequence No against each Drawing No so that you can keep count of how many revisions? For example;
Drawing No Sequence No (or Count)
123456-A 1
123456-B 2
123456-0 3
123456-1 4
123456-2 5
123456-3 6

Thanks

Matt
 
Hi Matt ,

If the drawing numbers are together , it is easy , but if they are spread out in random order , it is more difficult ; does your workbook have the following in any order of occurrence , as shown below ?

123456-2
123456-B
123456-0
123456-A
123456-3
123456-1

Even if they occur like this , you would still want them to be numbered correctly , as shown below :

123456-2 ... 5
123456-B ... 2
123456-0 ... 3
123456-A ... 1
123456-3 ... 6
123456-1 ... 4

Narayan
 
Hi Narayan,

That's correct. Formula would need to account for Drawing No's being in random order as you mentioned. I am using your previous formulas so maybe these might help you with a new formula to do this new task.

Matt
 
You must log in or register to reply here.
Back
Top