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

 

[willow1985]

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
[TABLE="width: 136"]
<tbody>[TR]
[TD]Next IAR#[/TD]
[TD="align: center"]#Value ![/TD]
[/TR]
[TR]
[TD]IAR #[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IAR 1001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IAR 1002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IAR 1003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IAR 1004[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Some of the IAR data has links on them. Dont know if that matters...

 

[Fluff]

Did you see post#9?

 

[willow1985]

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

 

[willow1985]

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

 

[Fluff]

So is everything ok now?

 

[willow1985]

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!

 

[Fluff]

Glad to help & thanks for the feedback

 

[willow1985]

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

 

[Fluff]

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.

 

[willow1985]

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