I don't see anything like this posted already - so here's my dilema.

Contract numbers start with various letters based on the type of contract
they are (F, Q, H, M). They then go in numerical order per letter. example
of the format: F-04. I want a formula to look at an entire columns of data
(about 200 rows) and tell me what the last contract number used for that
letter is. For example I have to enter a new contract that needs to start
with a Q. I want the formulas to look in this column and find the largest Q
contract and add one to it so that I know what the next available number is.
If this is even possible I'm pretty sure it is an array. Thanks for your
help.

Try this array formula** :

=TEXT(MAX(IF(LEFT(A2:A20)="F",--MID(A2:A20,3,5))),"00")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Assuming the numbers are no more than 5 digits. If they are increase the 5
in the MID function as needed.

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
I don't see anything like this posted already - so here's my dilema.

Contract numbers start with various letters based on the type of contract
they are (F, Q, H, M). They then go in numerical order per letter.
example
of the format: F-04. I want a formula to look at an entire columns of
data
(about 200 rows) and tell me what the last contract number used for that
letter is. For example I have to enter a new contract that needs to start
with a Q. I want the formulas to look in this column and find the largest
Q
contract and add one to it so that I know what the next available number
is.
If this is even possible I'm pretty sure it is an array. Thanks for your
help.

Hi JICDB,

As long as they go in order and don't skip numbers this will work. You can
move things around as you like.

If your list is in column B, and if C1 contains the letter you want to assign,
in D1 put:

=COUNTIF(B:B,C1&"*")+1

D1 will display the next number.

Let me know if this works for you.

Squeaky

"JICDB" wrote:

I don't see anything like this posted already - so here's my dilema.

Contract numbers start with various letters based on the type of contract
they are (F, Q, H, M). They then go in numerical order per letter. example
of the format: F-04. I want a formula to look at an entire columns of data
(about 200 rows) and tell me what the last contract number used for that
letter is. For example I have to enter a new contract that needs to start
with a Q. I want the formulas to look in this column and find the largest Q
contract and add one to it so that I know what the next available number is.
If this is even possible I'm pretty sure it is an array. Thanks for your
help.

Works perfect!!! Thanks so much. I really appreciate all of the help I get
from you and others like you who impart their wisdom. Have a great day!

"Squeaky" wrote:

Hi JICDB,

As long as they go in order and don't skip numbers this will work. You can
move things around as you like.

If your list is in column B, and if C1 contains the letter you want to assign,
in D1 put:

=COUNTIF(B:B,C1&"*")+1

D1 will display the next number.

Let me know if this works for you.

Squeaky

"JICDB" wrote:

I don't see anything like this posted already - so here's my dilema.

Contract numbers start with various letters based on the type of contract
they are (F, Q, H, M). They then go in numerical order per letter. example
of the format: F-04. I want a formula to look at an entire columns of data
(about 200 rows) and tell me what the last contract number used for that
letter is. For example I have to enter a new contract that needs to start
with a Q. I want the formulas to look in this column and find the largest Q
contract and add one to it so that I know what the next available number is.
If this is even possible I'm pretty sure it is an array. Thanks for your
help.