Page 1 of 2 12 LastLast
Results 1 to 10 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. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

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

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

    How about

    ABC
    2IAF 1234
    3IAF 1201
    4IAF 1300
    5IAF 1001IAF 1301

    Sales



    Array Formulas
    CellFormula
    C5{="IAF "&MAX(IF(LEFT(A$2:A5,3)="IAF",RIGHT(A$2:A5,4)*1))+1}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - 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. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    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

    How would you write this so if looks in all of column A? This stops at A5

  4. #4
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    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

    Why does it start at A2? My data starts at A1 however when I change it to such it gives me the result of IAF 1235

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

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

    It looks in A2 because I assumed that you would have a header and you never said where your data was located.
    s it's an array formula you should not look at entire columns, just change the rows as required.
    - 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. #6
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    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 added a header so my data now starts at A2.

    Other users will be using the spreadsheet and will not adjust the formula as they add new form numbers. Is there anything that can reference an entire column? I also tried to change the formula to: ="IAF "&MAX(IF(LEFT(A$2:A100000,3)="IAF",RIGHT(A$2:A1000000,4)*1))+1 however now I am getting an #N/A.

    I required a more complicated formula awhile ago and PeterSSs provided a formula that looked like this (see below), although I still dont particularly understand it to be able to modify it for what I require now.
    The data for the below formula from Peter only involved 1 letter and referenced another cell to determine the letter. This time there is 3 letters and they will always be the same (IAF)

    =IF(C$1="","",C$1&AGGREGATE(14,6,MID(A$1:A$22,2,9)/(LEFT(A$1:A$22,1)=C1),1)+1)

    Could this be adapted to what I am looking for?
    Last edited by willow1985; Sep 18th, 2019 at 10:46 AM.

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

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

    You're ranges are not the same size, which they must be.
    However you should limit array formula to the required range wherever possible, with the ranges you are using it will seriously affect the performance of the sheet.
    - 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. #8
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    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

    This formula is not an array formula though?? =IF(C$1="","",C$1&AGGREGATE(14,6,MID(A$1:A$22,2,9)/(LEFT(A$1:A$22,1)=C1),1)+1)

    I am new to this and am still learning.

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

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

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

    But whilst it is not array entered, it still process every single cell in the range, which will still be slower than limiting it to a sensible range.
    - 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. #10
    Board Regular
    Join Date
    Jul 2019
    Posts
    178
    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 missed a zero. Yes this formula is working now:="IAF "&MAX(IF(LEFT(A$2:A1000000,3)="IAF",RIGHT(A$2:A1000000,4)*1))+1.

    Thank you very much!

    Carla
    Last edited by willow1985; Sep 18th, 2019 at 11:07 AM. Reason: to say thank you

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
  •