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

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

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

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

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

Did you see post#9?  Reply With Quote

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

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

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

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

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

So is everything ok now?  Reply With Quote

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

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

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

Glad to help & thanks for the feedback  Reply With Quote

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

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

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

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

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

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