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
 
Nevermind. I applied the formula to the real spreadsheet and I get a #Value error. The data was a bit different "IAR" not IAF but that shouldnt have broken the formula...

The formula I am using in Cell B1: ="IAR "&MAX(IF(LEFT(A$3:A1000000,3)="IAR",RIGHT(A$3:A1000000,4)*1))+1
The data starts in A3

Here is how the data looks:

Column A Column B
Next IAR##Value !
IAR #
IAR 1001
IAR 1002
IAR 1003
IAR 1004

<tbody>
</tbody>


Some of the IAR data has links on them. Dont know if that matters...
 
Last edited:
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Figured it out. There was inconsistent data. One of the IAR numbers someone entered as IAR 1012A....I am not sure how to account for that occurrence however...
 
Upvote 0
I had not tried that new formula. was still working with your previous one. I just tried the: ="IAF "&AGGREGATE(14,6,MID(A$2:A$22,4,9)/(LEFT(A$2:A$22,3)="IAF"),1)+1
and it does work very well and the "A" at the end of one of the form numbers doesn't seem to affect it
 
Upvote 0
Everything is perfect. Thank you very much, I have learned so much from you and the guys on this website. I will have to look at the store and see if there are any books I can purchase or possibly seminars I can attend. Again you guys are great and thank you!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
If you have one more moment, I would like to understand the formula better.

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

"IAF " : is referencing what the number is starting with
&AGGREGATE : allows you to combine a bunch of elements while skipping errors or blanks

Not sure what the 14 or 6 references

MID : Extracts text from inside a string however I thought it was structured as: Text,start num,num of characters but above looks like Range, number of text characters (ATF[space]), and total number of characters, although I count 8 and not sure why it is 9.
/ : Unsure why it is divided
LEFT : extracts text left of a string so IAF but again I thought that was structured as (text, num chars)
,1 : I am unsure what this references
+1 : tells the formula to add 1 to the next number it finds

I still have a lot to learn.

Thank you

Carla

 
Upvote 0
I find that the easiest way to understand a formula is to use the "Evaluate Formula" feature on the formulas tab.
Limit the range of the formula to a few rows & with that cell selected step through the evaluate & you can see what is going on & in what order.
 
Upvote 0
I still have trouble understanding the Evaluate formula feature. I googled some of the formula and see that 14 seems to be related to a Large formula and 6 ignores values. I will have to do some more research when I have the time.

Thank you for your help

Carla
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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