Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

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

  1. #11
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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...
    Last edited by willow1985; Sep 18th, 2019 at 11:33 AM. Reason: Additional info on data

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,079
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

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

    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

  3. #13
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

  4. #14
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,079
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

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

    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

  6. #16
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,079
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

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

    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

  8. #18
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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


  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,079
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default 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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #20
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •