Excel Part Counter (Incremental)

[danmachen1]

Hi Guys,

I am trying to create a formula that can find the next usable number in a column of part numbers.
In cell A1 "EC" is entered.
In cell A2 I want the answer to the next available part number in the sequence.

in Cells C2:C20000 there are various part numbers which continue to be entered continuously.

EC102
EC105
EC100
EC088 ETC.

The current formula I have is:
=A1&TEXT(SUBSTITUTE(LOOKUP(2,1/FIND(A1,C1:C20000),C1:C20000),A1,"")+1,"000")

But this would give EC089 as the next part number. I want the result to give EC106 as the next part number as this is the next one available.

Any help would be great. This needs to be a single line solution and not a macro. The only other fix I have is to sort the columns in order before counting

 

[slinky]

In B1
=LARGE(IF(LEFT(C2:C2500,2)=A1,(RIGHT(C2:C2500,3)*1),""),1)+1
I've tested it a few different ways, it should work nicely.
(note the absence of the TEXT function)

 

[danmachen1]

In B1
=LARGE(IF(LEFT(C2:C2500,2)=A1,(RIGHT(C2:C2500,3)*1),""),1)+1
I've tested it a few different ways, it should work nicely.
(note the absence of the TEXT function)

Yes!! Great!

I have noticed the absence of "text", that simplifies it loads. But I may need text to format the number to 3 digit, i.e. text(A1,"000") or similar.

The final thing would be to try and add the EC letters back into the number.

I have in cell A2 ..... =CONCATENATE(A1&(TEXT(A2,"000")))

Can this be added into B2 formula somehow???

 

[slinky]

Totally doable, out at the mo but will sort it when I return.

 

[slinky]

You have mail.
New formula is {=A1&TEXT(LARGE(IF(LEFT(C2:C2500,2)=A1,(RIGHT(C2:C2500,3)*1),""),1)+1,"000")}