# Thread: Formula to determine next sequence of number starting with 3 letters and in random order Thanks: 0 Likes: 0

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

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

2. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

ABC
2IAF 1234
3IAF 1201
4IAF 1300
5IAF 1001IAF 1301

Sales

Array Formulas
CellFormula
C5{="IAF "&MAX(IF(LEFT(A\$2:A5,3)="IAF",RIGHT(A\$2:A5,4)*1))+1}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

3. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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

4. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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

5. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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.

6. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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?

7. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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.

8. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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.

9. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

="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.

10. ## Re: Formula to determine next sequence of number starting with 3 letters and in random order

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