# Find MIN and MAX of numbers and text

#### 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

#### matt-gilbert

##### Member
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

#### NARAYANK991

##### Excel Ninja
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

#### matt-gilbert

##### Member
Hi Narayan,
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

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

Can you try your data on this file ?

Narayan

#### Attachments

• 9.1 KB Views: 2

#### matt-gilbert

##### Member
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

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

See if this works.

Narayan

#### Attachments

• 11.7 KB Views: 2

#### matt-gilbert

##### Member
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

• 9.9 KB Views: 2

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

See if this is OK.

Narayan

#### Attachments

• 12 KB Views: 2

#### matt-gilbert

##### Member
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

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

OK. In case of the minimum version what should be the output ?

Narayan

#### matt-gilbert

##### Member
Hi Narayan,

If there is no earlier revision of a Drawing No, then output should be "-".

Matt

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

See if this is OK.

#### Attachments

• 12.2 KB Views: 1

#### matt-gilbert

##### Member
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

#### matt-gilbert

##### Member
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

#### NARAYANK991

##### Excel Ninja
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

#### matt-gilbert

##### Member
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

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

See if this works.

Narayan

#### Attachments

• 12.4 KB Views: 3

#### matt-gilbert

##### Member
Hi Narayan,

Perfect. Thanks again.

Matt