Did you see post#9?
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...
Last edited by willow1985; Sep 18th, 2019 at 11:33 AM. Reason: Additional info on data
Did you see post#9?
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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...
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
So is everything ok now?
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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!
Glad to help & thanks for the feedback
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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
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.
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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
Like this thread? Share it with others