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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
How about


Book1
ABC
2IAF 1234
3IAF 1201
4IAF 1300
5IAF 1001IAF 1301
Sales
Cell Formulas
RangeFormula
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.
 
Upvote 0
How would you write this so if looks in all of column A? This stops at A5
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top