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]

This works in my very simple example here..

=LARGE(TEXT(RIGHT(A1:A4,3)*1,"###")*1,1)+1

Entered as a CSE formula.

Well, it returns the next available value in the range. Do all of the part numbers have an EC prefix?

 

[slinky]

Re-instated..

---

CSE Formula
=LARGE(IF(LEFT(A1:A8,2)=D1,(TEXT(RIGHT(A1:A8,3)*1,"###")*1),""),1)+1

List in A1:A8
Candidate part prefix in D1

 

[danmachen1]

I cant seem to make this work even entering with CSE.

Returns #Value!

 

[danmachen1]

Ah OK! I got it to work. But how does the formula work? How do I change the cell position that it references?

 

[danmachen1]

The data also doesn't always have "EC" in the dataset. Sometimes it can be SC, or AA etc.

 

[slinky]

OK.

=LARGE(IF(LEFT(A1:A8,2)=D1,(TEXT(RIGHT(A1:A8,3)*1,"###")*1),""),1)+1

In this example, D1 is the prefix, "EC" in the example provided.
A1:A8 is the list of part numbers.
TEXT is converting the ouput of RIGHT into number format, albeit that could probably be dropped as the *1 is converting it to a number anyway.
LARGE(Array,1) is returning the largest value in the given array.

Does that make sense?

 

[danmachen1]

Yes I understand but I can't figure out why the equation doesn't work if you move the reference cells somewhere else.

I have replaced the reference cells with some other cells and populated them with numbers but now the formula breaks down.

 

[slinky]

Send me an example if you want, I'm at my desk @ home for the next 30 minutes or so.
wildotperryatgmaildotcom

 

[danmachen1]

sent!