# Thread: Determining Next sequence of number if there are 7 sequences each starting with a letter Thanks:  2 Post #5322905 (1)Post #5323284 (1) Likes:  2 Post #5322905 (1)Post #5323284 (1)

1. ## 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  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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  Reply With Quote

4. ## 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  Reply With Quote

5. ## 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  Reply With Quote

6. ## 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?  Reply With Quote

7. ## 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?  Reply With Quote

8. ## 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  Reply With Quote

9. ## 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  Reply With Quote

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

Yes! This is perfect!!!

Thank you so much!  Reply With Quote

## User Tag List

find, letter, number, sequence, sequences 