Formula to determine next sequence of number starting with 3 letters and in random order

[willow1985]

Hello,

I have a list of form numbers that are in random order (see below example). Is there an excel formula that could check the entire column, only look at numbers starting with IAF and determine what the next form number would be?

IAF 1234
IAF 1201
IAF 1300
IAF 1001

So it would return: IAF 1301

Thank you for all of your help

Carla

 

[Fluff]

How about

Book1
	A	B	C
2	IAF 1234
3	IAF 1201
4	IAF 1300
5	IAF 1001		IAF 1301
Sales

Cell Formulas
Range	Formula
C5	{="IAF "&MAX(IF(LEFT(A$2:A5,3)="IAF",RIGHT(A$2:A5,4)*1))+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[willow1985]

How would you write this so if looks in all of column A? This stops at A5

 

[willow1985]

Why does it start at A2? My data starts at A1 however when I change it to such it gives me the result of IAF 1235

 

[Fluff]

It looks in A2 because I assumed that you would have a header and you never said where your data was located.
s it's an array formula you should not look at entire columns, just change the rows as required.

 

[willow1985]

I added a header so my data now starts at A2.

Other users will be using the spreadsheet and will not adjust the formula as they add new form numbers. Is there anything that can reference an entire column? I also tried to change the formula to: ="IAF "&MAX(IF(LEFT(A$2:A100000,3)="IAF",RIGHT(A$2:A1000000,4)*1))+1 however now I am getting an #N/A.

I required a more complicated formula awhile ago and PeterSSs provided a formula that looked like this (see below), although I still dont particularly understand it to be able to modify it for what I require now.
The data for the below formula from Peter only involved 1 letter and referenced another cell to determine the letter. This time there is 3 letters and they will always be the same (IAF)

=IF(C$1="","",C$1&AGGREGATE(14,6,MID(A$1:A$22,2,9)/(LEFT(A$1:A$22,1)=C1),1)+1)

Could this be adapted to what I am looking for?

 

[Fluff]

You're ranges are not the same size, which they must be.
However you should limit array formula to the required range wherever possible, with the ranges you are using it will seriously affect the performance of the sheet.

 

[willow1985]

This formula is not an array formula though?? =IF(C$1="","",C$1&AGGREGATE(14,6,MID(A$1:A$22,2,9)/(LEFT(A$1:A$22,1)=C1),1)+1)

I am new to this and am still learning.

 

[Fluff]

It can be adapted like
="IAF "&AGGREGATE(14,6,MID(A$2:A$22,4,9)/(LEFT(A$2:A$22,3)="IAF"),1)+1

But whilst it is not array entered, it still process every single cell in the range, which will still be slower than limiting it to a sensible range.

 

[willow1985]

I missed a zero. Yes this formula is working now:="IAF "&MAX(IF(LEFT(A$2:A1000000,3)="IAF",RIGHT(A$2:A1000000,4)*1))+1.

Thank you very much!

Carla