Identify the MAX number from series of Text and Number

[Amitsri_74]

I am tracking requirements for my project that has different identifier Text with a number. I want to get a max number for each category so that I can use that to assign the next number whenever I add a new requirement to the log. Here is the sample

USALL.01
USALL.02
USOA.01
USBO.01
USALL.03
USBO.02
USOA.02
....
....


I want to keep track of each category so that I know what was the last number I used for assignment and when row gets added the number should reflect the next number in the reference table

USALL - USALL.03
USOA - USOA.02
USBO - USBO.02

I know this is simple enough and can be done through array but I am not able to create an array by each category. Any help is highly appreciated.

Amit

 

[bosco_yip]

Try,



In D2, copied down :

=LOOKUP(1,-FIND(C2,A$2:A$100),A$2:A$100)

Regards
Bosco