Ascending Number Querry

I have a column of text thats a combination of text and a number
i.e, ABA1, ABA2, ABA3, BRB1, BRB2, BRB3, BRB4, CDC1, CDC2, CDC3 etc etc.
The text may be different but the number will always ascend.
At present I use some formula's that extract the number from the
text and use the MAX command to see which number I need to use
next. This column spreads 5000 rows and eats the mega-bytes in file
size! is there some VBA I can use to replace all the formula's that
checks the column and tells me which number to use next.

I have an additional request though, at present I can enter a prefix text
into a cell i.e "ABA" or "BRB" or "CDC" and which ever prefix I choose,
I am only told which next ascending number to use matching that prefix.

The formula's I have written work but takes up loads of file.

Can anybody help?

Don't know about VBA but perhaps you need a more efficient formula, if the text/numbers are in A1:A5000 then if you put a specific prefix in C2 this formula should give you the next in the series

=C2&LOOKUP(2^15,SUBSTITUTE(A1:A5000,C2,"")+0)+1

I'm assuming that each prefix's numbers are in order within A1:A5000 (even if mixed within other prefixes)

LOOKUP is generally an efficient formula because it uses quicker "binary search".

Are all your prefixes 3 letters?

Originally Posted by daddylonglegs

=C2&LOOKUP(2^15,SUBSTITUTE(A1:A5000,C2,"")+0)+1

That's a great formula

Hi there, the prefix is simply a code that could be 1, 2, 3 or 4 letters long.
I use the formula for an ordering system where new orders have to be a new number but the prefix could be the users initials. Every time that user creates a new order, then a new order number is listed and he uses that number after his/her prefix. I will try your method and would like to thank you for your help. Speak soon.

Originally Posted by sparx

Hi there, the prefix is simply a code that could be 1, 2, 3 or 4 letters long.
I use the formula for an ordering system where new orders have to be a new number but the prefix could be the users initials. Every time that user creates a new order, then a new order number is listed and he uses that number after his/her prefix. I will try your method and would like to thank you for your help. Speak soon.

OK,

I asked the question because there might be a problem should you have one prefix that repeats another, e.g. "AB" and "ABAB". Better to make a small change to the formula I suggested....

=C2&LOOKUP(2^15,SUBSTITUTE(A1:A5000,C2,"",1)+0)+1

Hello daddylonglegs, thanks for your reply - your first option did work but will use the 2nd version as advised - can your formula be addapted? could it look at 2 prefixes instead of one? In an earlier message I wrote that I have one column that increments on each order I make, but each order also produces a job number that may also be just a number or a number/text code - say I have 4 jobs running and code each as "Job1", "Job2", "Job3", "Job4", etc, I would like to first look at the job prefix "Job1", then look within that job prefix to see which next ascending order number to use. All the ascending numbers in my database are in column A3:A5002 and the jobs are in B3:B5002. Thanks for your help so far.