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

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,444
Office Version
365
Platform
Windows
Maybe something like
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ID</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B0001</td><td style=";">B</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">C001</td><td style=";">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">D001</td><td style=";">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">E001</td><td style=";">E</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">E002</td><td style=";">E</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">E003</td><td style=";">E</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">F001</td><td style=";">F</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B0002</td><td style=";">B</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">D002</td><td style=";">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">F002</td><td style=";">F</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">H001</td><td style=";">H</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">List</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">{=B2&TEXT(<font color="Blue">MAX(<font color="Red">IF(<font color="Green">LEFT(<font color="Purple">A$1:A1,1</font>)=B2,--MID(<font color="Purple">A$1:A1,2,4</font>)</font>)</font>)+1,IF(<font color="Red">B2="B","0000","000"</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
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

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,444
Office Version
365
Platform
Windows
If you only want to use C1 that would need a macro
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
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?
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,444
Office Version
365
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
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
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
456
Office Version
2019
Platform
Windows
Yes! This is perfect!!!

Thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,185
Messages
5,442,887
Members
405,204
Latest member
warviksam

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top