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  Reply With Quote

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  Reply With Quote

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  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

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

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?  Reply With Quote

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.  Reply With Quote

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.  Reply With Quote

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

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.  Reply With Quote

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  Reply With Quote

User Tag List

Tags for this Thread

form, formula, iaf, numbers, order  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•