Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Determining Next sequence of number if there are 7 sequences each starting with a letter

  1. #1
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Determining Next sequence of number if there are 7 sequences each starting with a letter

    Hello,

    I am looking for a formula that can determine the next sequence of a number from another sheet if there are 7 sequences each starting with a letter. For example:

    A001
    B001
    C001
    D001
    E001
    F001
    G001

    I know that formula: =MAX('Sheet1'!A$1:A$1000000)+1 works to find the next sequence of a number but I have 7 number sequences.
    I was thinking that when the user enters the correct letter (aka: G) in a certain cell that, maybe was a way to do an index/match and IF function to find the next sequence in the cells that match "G".

    Maybe someone will be able to help.

    Thank you

    Carla

  2. #2
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    all have 1 letter and 3 numbers with the exception of B actually. A,C,D,E,F,G all are 1 letter and 3 numbers and B is 1 letter and 4 numbers.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    Maybe something like
    AB
    1ID
    2B0001B
    3C001C
    4D001D
    5E001E
    6E002E
    7E003E
    8F001F
    9B0002B
    10D002D
    11F002F
    12H001H

    List



    Array Formulas
    CellFormula
    A2{=B2&TEXT(MAX(IF(LEFT(A$1:A1,1)=B2,--MID(A$1:A1,2,4)))+1,IF(B2="B","0000","000"))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Fluff; Aug 8th, 2019 at 03:21 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    For example, below is a list of document numbers (in column A). When I enter a letter A-G in cell C1 I want it to return the next sequence for that letter number combo. If I entered in "G" in Cell C1 it would return: G671. if I entered "E", it would return: E225 and so on

    G661
    G662
    G663
    G664
    G665
    G666
    G667
    G668
    G669
    G670
    D062
    D063
    D064
    D065
    D066
    D067
    D068
    E220
    E221
    E222
    E223
    E224

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    If you only want to use C1 that would need a macro
    - 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 willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    I dont understand. All I want to do is if you entered the document letter(aka G) in any cell, like C1, that the formula looks in column A, finds a match with any number starting with G and provides the next sequence number: G671. Is this not possible? Excel can determine the next value when you drag down and can find the next value when it is all numeric but not when it starts with a letter?

  7. #7
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    If I need a macro, could I apply a VBA code to a certain cell that is active as soon as you open the sheet? that as soon as you enter the letter in C1 it finds the next sequence of number and provides it in say cell D1?

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    Yes but if you have a formula that returns G261, when you change C1 to F you will get (for instance) F249.
    So the next time you put G into C1 you will get G261 again rather than G262
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,318
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    Does this array-entered** formula do what you want...

    =C$1&TEXT(MAX(IF(LEFT(A$1:A$22)=C$1,0+MID(A$1:A$22,2,4)))+1,MID("0000",1+(C$1<>"B"),4))

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular willow1985's Avatar
    Join Date
    Jul 2019
    Location
    Winnipeg, MB
    Posts
    223
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determining Next sequence of number if there are 7 sequences each starting with a letter

    Yes! This is perfect!!!

    Thank you so much!

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
  •