danmachen1
New Member
- Joined
- Feb 19, 2015
- Messages
- 23
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
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