Comments on: Excel SEQUENCE function - create a number series automatically

The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose. Continue reading

Comments page 7. Total comments: 439

  1. A pot can hold 1000 coins. I would like to display this as a primary key. For example,

    P1C1
    P1C2
    P1C3
    ...
    P1C999
    P1C1000
    P2C1
    P2C2
    ...
    P2C998
    P2C999
    P2C1000
    P3C1
    P3C2
    P3C4

    The list should have no bottom limit, allowing as many entries as possible.

    What is the best way to do this?
    Any help would be greatly appreciated!

    1. Hi!
      If I got you right, the formula below will help you with your task:

      ="P"&CEILING(SEQUENCE(4500,1,1,1)/1000,1)& "C" &TRUNC(MOD((SEQUENCE(4500,1,1,1)-1)/1,1000)+1)

  2. what would be the best formula to get gaps on this sequence
    220-0007
    220-0009
    221-0001
    223-0001
    223-0002
    223-0004
    to return the exact missing numbers
    i.e 220-0008 & 223-0003 are missing.

    1. what would be the best formula to get gaps on this sequence
      220-0007
      220-0009
      221-0001
      223-0001
      223-0002
      223-0004
      to return the exact missing numbers
      i.e 220-0008 & 223-0003 are missing.

  3. Hello Sir,

    Thanks for your help in advance. Every time a record is added to excel I have to generate a sequence number automatically. Sequence=Stage.Phase.Next Number - Can you please help how to do this in excel.

    Stage Phase Sequence
    1 1 1.1.1
    1 1 1.1.2
    1 2 1.2.1
    2 2 2.2.1
    2 3 2.3.1
    2 3 2.3.2

      1. Pattern is Stage.Phase.Next Incremental number in that Stage.Phase

        Stage.......Phase.......Sequence
        1000...........1..............1000.1.1
        1000...........1..............1000.1.2
        1000...........1..............1000.1.3
        2000...........2..............2000.2.1
        2000...........2..............2000.2.2
        1000...........2..............1000.2.1
        1000...........2..............1000.2.2

        Hope it makes sense...thank you!

        1. I did it successfully using COUNTIF. Thank you for your time!

  4. Correction to previous post -

    The format for both methods in my first post got off-alignment when I submitted.
    Here is my attempt to correct the formatting of the 2 methods so that it's readable (I hope it doesn't get messed up again).

    Method 1
    ========

    Tab Cell E1
    ==== ========
    01 01-Jan-2022
    02 =('01'!E1+1)
    03 =('02'!E1+1)
    04 =('03'!E1+1)
    05 =('04'!E1+1)

    Method 2
    ========

    Tab Cell E1
    ==== ========
    01 01-Jan-2022
    02 =('01'!E1+1)
    03 =('01'!E1+2)
    04 =('01'!E1+3)
    05 =('01'!E1+4)

  5. I was searching Google for a better way to do the following and your website popped up. I hope you can help and that it’s not something that can’t be done.
    I have a workbook for each month with either 28,30 or 31 tabs/sheets – one for each day depending on the month.
    I have a date field in the same cell (E1) for each tab - date format is dd-mmm-yyyy.
    I would like the same formula for cell E1 in each tab that increments the date without me having to edit the formula in cell E1 in each tab to change the tab name (Method 1) or increment number (Method 2) to point to the previous tab and add the increment number.
    I have the following 2 formulae that works but I have to change each tab (from tab ‘02’ onwards) to either point to the previous tab name and add 1 or add an increasing increment number to tab name ‘01’. Is there a way to specify the previous tab without having to actually name it – ie. is there a variable name that represents the previous tab ? Here is what currently works, but to me is a bit clumsy.
    Thanks, in advance, for any suggestions.

    Method 1

    Tab Name Cell E1
    ======== ==========
    01 01-Jan-2022
    02 =('01'!E1+1)
    03 =('02'!E1+1)
    04 =('03'!E1+1)
    05 =('04'!E1+1)
    etc etc

    Method 2 - it also works if I do this :

    Tab Name Cell E1
    ======== ===========
    01 01-Jan-2022
    02 =('01'!E1+1)
    03 =('01'!E1+2)
    04 =('01'!E1+3)
    05 =('01'!E1+4)
    etc etc

    1. Method 1

      Tab Name . . . . Cell E1
      ======== . . . . ===========
      01 . . . . . . . . 01-Jan-2022
      02 . . . . . . . . =('01'!E1+1)
      03 . . . . . . . . =('02'!E1+1)
      04 . . . . . . . . =('03'!E1+1)
      05 . . . . . . . . =('04'!E1+1)
      etc . . . . . . . . etc

      Method 2 - it also works if I do this :

      Tab Name . . . . Cell E1
      ======== . . . . ===========
      01 . . . . . . . . 01-Jan-2022
      02 . . . . . . . . =('01'!E1+1)
      03 . . . . . . . . =('01'!E1+2)
      04 . . . . . . . . =('01'!E1+3)
      05 . . . . . . . . =('01'!E1+4)
      etc . . . . . . . . etc

    2. Hello!
      You can only get the name of the previous worksheet using a VBA macro. Regular Excel formulas can only get the name of the sheet where the cell is located:

      =MID(CELL("filename",Sheet1!A2),SEARCH("]",CELL("filename",Sheet1!A2))+1,31)

  6. Hi, is it possible to arrange the sequence formula to add number until the end of the table and automatically add a number if the table row range is extended?

    1. Hello!
      In the first argument of the SEQUENCE function, you can use the COUNTA function, which will count the number of rows with data in the table. When adding. data in column A, the SEQUENCE function will add one more value.

      =SEQUENCE(COUNTA(A:A),1,1,1)

      I hope my advice will help you solve your task.

  7. I am attempting to create a series of mobile numbers. The first four digits and final two digits should not change.
    Example:
    567-000-042
    567-000-142
    567-000-242
    567-000-343

    The series should create a list of all possible combinations from 567-000-042 to 567-999-942 (only the 4th-7th digits will change. The first 3 digits and final two digits should not change.) Thank you.

    1. Hello!
      Use the TEXT function to write the number in the desired format.

      ="567-"&TEXT(CEILING(SEQUENCE(1000,1,0,1),1),"000-0")&"42"

      I hope my advice will help you solve your task.

  8. Hi there,
    I need to create a sequential list of barcode numbes - 20 digits, the first two digits being "00"
    What do i need to do to the formula to make that come out, without getting rid of the two "00" at the front of the number

  9. HELLO SIR
    EMP ID Generate :- NAME WITH NUMBER
    PRAKASH P0001
    PRATIK P0002
    POOJA P0003
    I don't know how I can.

  10. How do I write a formula to have 0 to 9 followed by A to Z in a sequence and then to be copied to let's say copied to 180 rows?
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    0
    A
    B
    C
    etc..

  11. I want to create following sequence
    01-01-21
    01-01-21
    01-01-21
    02-01-21
    02-01-21
    02-01-21
    03-01-21
    03-01-21
    01-01-21
    and so on

  12. i need to create chart,
    1 2 3 4 5
    6 7 8 9 10
    11 12 13 14 15
    16 17 18 19 20
    leave a blank and then generate
    21 22 23 24 25
    26 27 28 29 30
    31 32 33 34 35
    36 37 38 39 40
    blank cell
    41 42 43 44 45
    46 47 48 49 50
    51 52 53 54 55
    56 57 58 59 60
    suggest pls

  13. Hello,

    I have a question regarding the "n" in the SEQUENCE Function.
    Can it be unknown?

    From a table, I would like the formula to list all the data that are above 13%. I can do this using the INDEX, SORT and FILTER functions. But I do not know how many people will be above 13%. Is there a solution to this?

    My formula is as per the below:

    =INDEX(SORT(FILTER(Master!F4:AC248,Master!AC4:AC248>=13%),24,-1),SEQUENCE(20),{1,24})

    Thanks,

    Matt

    1. Hello!
      In the SEQUENCE function, specify the maximum number of values (for example, 245). To ignore errors, use the IFERROR function.

      =IFERROR(INDEX(SORT(FILTER(Master!F4:AC248,Master!AC4:AC248>=13%),24,-1),SEQUENCE(245),{1,24}),"")

      Hope this is what you need.

      1. Hi Alexander,

        Thank you for taking the time to reply and apologies for the delay of my answer.

        Unfortunately this does not work for me so had to change the formula to the below:

        =INDEX(SORT(FILTER(Master!B4:AC243,(Master!S4:S243="GBP")*(Master!M4:M243=""),"No results"),28,-1),SEQUENCE(20),{5,28})

        So what this does is giving me the 20 top percentages. But only 8 of them are above 13%.
        How can I make sure that with this formula, it removes the percentages that are under 13%?
        (if this is possible).

        Thanks,

        Matt

        1. Hi!
          Without seeing your data it is difficult to give you any advice.
          If you are fetching data from column AC, add a filter condition to the formula:

          FILTER(Master!B4:AC243,(Master!S4:S243=”GBP”)*(Master!M4:M243=””)*(Master!AC4:AC243>13%),”No results”)

          I hope I answered your question.

          1. Hi Alexander,

            Works a treat! Thank you so much!

            Matt

  14. I want to enter the following in the column , so kindly help me out:

    A1 : 0-1
    A2 : 1-2
    A3 : 2-3

    and so on.
    whereas A1, A2, A3 are the address of the Column and the numerics are the value to be entered.

  15. Hello

    I would like to have this numbering sequence:
    01-G
    02-G
    03-G

    Thanks in advance for the help

    1. Hello!
      You can use this formula:

      =TEXT(SEQUENCE(10,1,1,1),"00")&"-G"

      Add text to the sequence of numbers using the & operator.

  16. Hi Sir,

    I would like to make following sequence, which would be changing based on names in other column. The number of rows with the same name is random - sometimes 5 rows, sometimes 1 row:

    2021-1000 John
    2021-1000 John
    2021-1000 John
    2021-1001 Tim
    2021-1001 Tim
    2021-1001 Tim
    2021-1002 James
    2021-1002 James
    2021-1003 Michael

    I would appreciate your help.

    1. Hi!
      What you want to do is not sequence. This is a random number of duplicates. I'm really sorry, looks like this is not possible with the standard Excel options.

  17. Hello, I am trying to make a SEQUENCE based on the text in the cell next to it.

    Column B can be either IC, IG, IB, or any other 2 letter
    Column C is a number list 21001 21002 21003 and so on.

    However the number can repeat based on the letters in Column B. So I need the sequence to be based on the next available number that relates to the letters in column B.

    I hope that make sense

    1. Hello!
      To make a sequence of numbers with a condition, use the formula

      =21000+COUNTIF($B$2:B2,B2)

      Hope I understood the problem correctly.

      1. It did work thank you. Now if I can get my co workers to stop skipping number

        1. Hello I had a follow up on this. I changed the formula a little to exclude texts from the count so it looks like this now.
          =21000+COUNTIFS('2022'!$D$3:D3,D3,'2022'!$K$3:K3,"CO",'2022'!$K$3:K3,"NCCO")+1
          My problem is when I go to the next cell the is jumps numbers equal to how many cells are used
          EX.
          =21000+COUNTIFS('2022'!$D$3:D5,D4,'2022'!$K$3:K5,"CO",'2022'!$K$3:K5,"NCCO")+1
          =21000+COUNTIFS('2022'!$D$3:D7,D5,'2022'!$K$3:K7,"CO",'2022'!$K$3:K7,"NCCO")+1
          =21000+COUNTIFS('2022'!$D$3:D9,D6,'2022'!$K$3:K9,"CO",'2022'!$K$3:K9,"NCCO")+1
          ^ ^ ^
          I put arrows where the numbers jump. Why would it do that and not follow the order? I can not seem to stop this.

  18. I am constantly having to create numbering sequences that look like this:

    100 000001
    100 000002

    three numbers, space and then 6 numbers with only the last 6 numbers increasing. What would be the formula to achieve that?

    Thanks

  19. I am in need of a way to have a number increase by one each day for a production report. Example

    4 departments in separate columns. Above each heading is the number of days worked. Is there a way increase those days by 1 automatically each day?

    Thanks in advance!

  20. Is there a way to sequence numbers if the numbers look like this------

    PI-2021-07-0480
    PI-2021-07-0481

    Please advise -
    Dawn

  21. Hi,

    what if I have number series like
    [1,2,3,4,5], [11,12,13,14,15], [21,22,23,24,25] and so on in one column until some hundred,
    is there any way to do this?

    thank you!

  22. Good Day

    We would like to have the following data on a separate line for a raffle draw. They unfortunately did the numbering as :

    31100-31127
    32656-32697
    34729-34750
    90784-90828
    92159-92198
    333-336
    851-856
    116-119
    867-858
    859
    2070-2079
    2709-2713
    2569-2573
    337-340
    341-343
    860
    861-861

    Is there a way to have them in one column in order?

      1. Hello,
        I suggest a helper column in column B with this formula (and copy down):
        =IF(ISNUMBER(A1),A1,VALUE(LEFT(A1,FIND("-",A1)-1)))

        Then in cell C1, have a sort formula =SORT(A1:B1000,2)

        Hope this solves the problem.

        André

  23. Hello sir, how about sequence with text in rows, like:

    var 1 var 2 var 3

    And so on.

    Thank you for your help

    1. Hello!
      Please re-check the article above since it covers your task. The SEQUENCE function works on a row as well.

      1. I've tried

        ="var "&SEQUENCE(1,50,1,1)

        But it doesn't work

  24. how about

    KD1-20
    KD21-40
    KD41-60

    and so on.

    tnx

    1. Hi!
      Try the following formula:

      ="KD"&CEILING(SEQUENCE(100,1,1,20)/1,1)&"-"&CEILING(SEQUENCE(100,1,1,20)/1,1)+19

      1. i forgot something formula for

        1kd 1 - 20
        2kd 21 - 40
        3kd 41 - 60
        4kd 61 - 80

        thank u sir godbless

  25. Hello Sr

    how can I do a sequence of rows every 8 repetitive number, every 8 rows same number, then the next one 8 times, etc. Example: the patern is same number 8 rows or 8 times, then next number ther 8 rows or times:
    1
    1
    1
    1
    1
    1
    1
    1
    2
    2
    2
    2
    2
    2
    2
    2
    3
    3
    3
    3
    3
    3
    3
    3
    thank you

      1. Thanks

      2. thank you

  26. I am currently stuck on a problem while creating an MLB pitching model. I would like to distribute the number of batters faced across the 9 positions in a batting order. Lets say a pitcher faces 23 batters during his out outing I would like to distribute that across the 9 batting order spots in sequential order. In doing this manually I know the pitcher would face the first 5 batters 3 times and the 6-9th batter 2 times. How can I create a running tally that distributes those 23 batters across the 9 batting order spots -- more simply put how can I tally the number 23 across 9 cells in sequential order? Thank you in advance if you can solve this riddle!

  27. I want to generate the following pattern in excel cells. Would it be possible
    0000000000
    1000000000
    1100000000
    1110000000
    1111000000
    ....

  28. I want to generate this kind of number but I am having difficulty. Anyone can help me?

    =VLOOKUP(G2,O2:P23,2,FALSE)
    =VLOOKUP(G3,O2:P23,2,FALSE)
    =VLOOKUP(G4,O2:P23,2,FALSE)
    =VLOOKUP(G5,O2:P23,2,FALSE)
    =VLOOKUP(G6,O2:P23,2,FALSE)
    =VLOOKUP(G7,O2:P23,2,FALSE)
    =VLOOKUP(G8,O2:P23,2,FALSE)
    =VLOOKUP(G9,O2:P23,2,FALSE)
    =VLOOKUP(G10,O2:P23,2,FALSE)

    The G Is the one only rising. Thank you to anyone who can help.

  29. Hi,

    1 have 5 digits number, for example 01234, from that number I want to generate 2 digits number for all the possible sequence, so 00, 01, 02, 03, 04, 10, 11, 12, 13, 14, 20, 21,... and so forth.

    Any way to do this? TYIA

  30. would it be possible to sequence numbers but end on the number that is in the next column.
    example:
    1 6
    2 6
    3 6
    4 6
    5 6
    6 6
    1 3
    2 3
    3 3
    1 5
    2 5

    We are using the info for a data merge for bundles. so the outcome would be "Bundle 1 of 6"

    Thank you

    1. Hello!
      You have not written what is the source data. I am assuming column B. Perhaps this formula will work -

      =COUNTIF($B$1:B1,B1)

      After that you can copy this formula down along the column.
      If this is not what you wanted, please describe the problem in more detail.

      1. Sorry, Can you tell I don't know what I'm doing?

        I was able to plug this in and worked. But I do see when I scroll down and some of the "bundles" are combined together and then go back to correct sequence. what could cause this?

        example: Source is column B.
        1 8
        2 8
        3 8
        4 8
        5 8
        6 8
        7 8
        8 8
        8 7
        9 7
        10 7
        11 7
        12 7
        13 7
        14 7
        1 9
        2 9
        3 9...

          1. The formula used was =COUNTIF($B$1:B1,B1)
            After some testing I realized that the repeating bundles would start from the previous similar bundles. see below.

            example:
            1 5
            2 5
            3 5
            4 5
            5 5
            1 7
            2 7
            3 7
            4 7
            5 7
            6 7
            7 7
            6 5
            7 5
            8 5
            9 5
            10 5

  31. What if we want this
    1 6 11
    2 7 12
    3 8 13
    4 9 14
    5 10 15

  32. Sequence reference for Bookmark pdf
    1st row is 1 - 5 (sequence)
    2nd row is 2 - 2
    3rd row is 2 - 4
    4th row is 4 - 5
    5th row is 6 - 7 (sequence)
    6th row is 8 - 9 (sequence)
    7th row is 9 - 11
    8th row is 10 - 11 (sequence)
    9th row is 14 - 15 (sequence)

    How to check missing sequence 12 - 13
    For above type of page range.

  33. Hello,
    I am trying to have a One Column, with the following parameters:

    First 20 Columns (1-20) is 1
    Next 30 Column (21-50) is 2
    Next 40 Column (51-90) is 3 and so forth.

    Also I want to make it dynamic where I can change the columns i.e Instead of having the first 20 columns to 1, I can plug into a cell 30 and the first 30 column is 1 and the rest adjust accordingly.

    I am create a training plan and I can decide to have the first 20 go to training in Wave 1 or first 30 to Wave 2 etc.

    Please how can I create this formula.

    Thanks for your help.

    Oluseyi

  34. This is perfect - Thank You!

  35. how can I generate this kind of Sequence

    Chapter: 1 - 3
    Chapter: 4 - 6
    Chapter: 7 - 9
    .
    .
    .
    .

  36. Hello,

    What would the formula be to create this sequence?

    A1001A A1001B A1001C A1001D A1002A A1002B A1002C A1002D A1003A A1003B A1003C A1003D

    A1004A A1004B A1004C A1004D A1005A A1005B A1005C A1005D A1006A A1006B A1006C A1006D

    Where the last letter in the series ends with "D", but the preceding number will increase as the sequence proceeds.

    Thanks

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      ="A"&(1000+CEILING(SEQUENCE(1000,1,1,1)/4,1)) & CHOOSE(TRUNC(MOD((ROW(A1:A1000)-1)/1,4)+1),"A","B","C","D")

      Hope this is what you need.

  37. I really prefer the sequence function. However, my computer, Microsoft 2016, doesn't exist the function. I accessed to the internet and i found that Sequence Function can be only supported by Microsoft 360. How can i install the function and does it affect my computer if i install it? Thank you very much

  38. Hi thank you so much for your informative guidance.

    This is pdf index page sequence.
    1-4
    5-88
    (37-38
    60-61
    61-63
    64-65
    64-64)
    89-101
    (91-92
    94-95)
    103-108
    107-108

    How to check missing sequence 102...

    eg. sequence of numbers 1-1, 2-2, 4-5. Number 3 was skipped..

  39. I have 4 sheets , the number of rows is random for each sheet. How to make the sequential number continue from sheet 1 to sheet 4 meanwhile the number of rows is random .

    1. Hello!
      Find the maximum number on the previous sheet using the MAX function and add 1 to it.

      =MAX(Sheet2!A:A)+1

      Then, on the current sheet, simply add 1 to this number.

  40. Hello,

    I hope you can help. I am trying to identify the number of transactions per date and per supplier, and create a sequence from this.

    For example, on 20th May 2021, 1 transaction was from British Gas, 3 transactions were put through from Amazon, and 1 from Sainsbury's. I assume a formula can cluster per unique supplier whilst incrementing sequentially, I just haven't cracked it yet:

    20210520-01
    20210520-02
    20210520-02
    20210520-02
    20210520-03

    Essentially, I want to match 3 transactions to one from a Cash Book to a Bank Statement. The CB would list 3 individual items from Amazon and the Bank would show one transaction. I would like to be able to cross-reference automatically/dynamically. That way I can SUMIF and MATCH to reconcile (hopefully).

    If there is a better way, please say! Currently, I am working on historic 'chunks' of formula and concatenating. I thought it was time to get up to date.

    Also, I can only get the date sequence as 44336 when appearing in a formula. That's fine if it has to be this way, I'd just prefer it to be Gregorian if possible. Do you know if I can use a format for this?

    Much appreciated in anticipation.

  41. I want sequence something like this:

    1
    2
    1
    2
    1
    2
    1
    2
    .
    .
    .
    .
    .
    etc.

    how to achieve this?

    1. Hi!
      A sequence is a series of consecutive numbers: 1,2,3 ... or 3,5,7 ... Each next number is not less than the previous one.
      Your example is not a sequence. Write 1 and 2 in the cells and copy them down the column.

  42. Hello,

    I have a sequence generated say in one column on one sheet (List A)

    Now I want to assign the numbers from this list A at various place on different sheet. Is there a way to every time assign number from this list A which is not already assigned (taken up earlier)

    - For now I have created a named list B combining all ranges in which I have assigned (manually) the number from from this list A.
    - Then in next column to list A put in a look up for each sequence number from this named list B to indicate which one is used up and which one not using 1 an 0. lets say this is indicator column
    - Further in one cell on sheet where i assign the numbers, put a formula to look up in this indicator column to find the first 0 and correspondingly return the sequence number against it. thus displaying the next number available for assignment.
    - I then manually assign (type) that displayed number from above cell and since that cell where i type the number is in the range included in list B, the cell displaying the next sequence updates to next one and so on continues.

    Is it possible to automatically do this instead of me typing it manually. If we can identify the formula then it would be great.

  43. i want a sequence where number is skipped
    eg. sequence of days where sunday is skipped

    1
    2
    3
    4
    5
    6
    8
    9
    10
    11
    12
    13
    15 ......

    1. Hello!
      The formula below will do the trick for you:

      =SEQUENCE(300,1,1,1)+CEILING(SEQUENCE(300,1,1,1)/6,1)-1

      Hope this is what you need.

  44. Greetings.
    I want to make sequence like these:
    15x number 1
    53x number 2
    45x number 3
    ...

    How can I do this?
    Than you in advance

  45. DEAR SIR
    I want to make sequence in different cells i mean selected cells .
    make select cells and put sequence in selected cell .
    not upcoming cell.

    1. Hi!
      An Excel formula can only work over a continuous range. It is not possible to select individual cells.

  46. I have a sequence of numbers, let's say:

    1
    3
    4
    20
    22

    and would like Excel to return on a cell(another sheet) the next available number in the given column, starting in 1 to n, if this makes sense.

    Is this possible?
    Thanks

    1. Hello!
      Write down your numbers starting at cell A2. Write this formula in cell B2

      =MIN(FILTER(SEQUENCE(100,1,1,1),(SEQUENCE(100,1,1,1)>A2)*(SEQUENCE(100,1,1,1)<>A3)*(SEQUENCE(100,1,1,1)<>B1)))

      and then copy it down along the column.
      You can learn more about FILTER function in Excel in this article on our blog.
      Hope this is what you need.

  47. i have a sequence of number

    AAA0001 --- AAA9999 , after the 9999 the next number i need is AAB0001 and continue increasement .

    please help , and the alphabet increase is without "I" and "o" .

    thanks !

    1. ok I have refined my previous answer a little
      set $C$2 to either 999 or 9999 depending on the number series you want
      then use this formula to get the series you asked for.
      No letters i or o and skip from 9999 to 0001

      This was a fun puzzle!!!

      =IF(SEQUENCE(1048576)>$C$2*24^2,"B","A") & CHAR(65 + INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24)) + IFS(INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24))>12,2,INT(MOD(SEQUENCE(1048576)/(24*$C$2)- 0.000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD(SEQUENCE(1048576)/($C$2)- 0.000001,24)) + IFS(INT(MOD(SEQUENCE(1048576)/($C$2)- 0.000001,24))>12,2,INT(MOD(SEQUENCE(1048576)/($C$2)-0.000001,24))>7,1,TRUE,0)) & RIGHT("00000" & ROUNDUP(MOD(SEQUENCE(1048576),($C$2)+0.000001),0),LEN(C2))

      1. for even more fun ;o)
        you can use this formula and drag from a1 to k1 or more
        This will continue the series since column limit in excel doesn't go far enough for your series
        in this case the number series 999 or 9999 is in $M$2

        =CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24^2*$M$2)- 0.0000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.0000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/(24*$M$2)- 0.000001,24))>7,1,TRUE,0)) & CHAR(65 + INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)- 0.000001,24)) + IFS(INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)- 0.000001,24))>12,2,INT(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576))/($M$2)-0.000001,24))>7,1,TRUE,0)) & RIGHT("00000" & ROUNDUP(MOD((SEQUENCE(1048576)+((COLUMN()-1)*1048576)),($M$2)+0.000001),0),LEN($M$2))

    2. Hi,
      I'm really sorry, we cannot help you with this task. Your task cannot be accomplished with Excel formulas.

      1. I'm spending way too much time on this but who doesn't like a good excel challenge.
        after playing with the start value I was able to make it a bit more elegant
        this solves the no i and o in the letter sequence and skips from 9999 to 0001
        also you can change the number series from 9999 to 999 in P2
        you can drag the top cell across to continue the series in the next column as this series exceeds the max rows allowed in a column.

        =CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24^2*$P$2),24)>7,1,TRUE,0)) & CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/(24*$P$2),24)>7,1,TRUE,0)) & CHAR(65 + MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24) + IFS(MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24)>12,2,MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1)/($P$2),24)>7,1,TRUE,0)) & RIGHT("00000" & MOD(SEQUENCE(1048576,1,(COLUMN()-1)*1048576,1),($P$2))+1,LEN($P$2))

  48. Hello,

    I have a sequence of numbers, let's say:

    1
    3
    4
    20
    22

    and would like Excel to return on a cell(another sheet) the next available number in the given column, starting in 1 to n, if this makes sense.

    Is this possible?
    Thanks

  49. Hi, thank you, Sir Alexander, I'm working on making a sequence of Admission number like
    1910404001
    19104040002
    1910404003
    ,,,,
    ,,,,,
    ,,,
    Please help me, thank you?

    1. Hi,
      The constant 1910404 can be combined with a changing value using the & operator. To always have 3 digits in a number, use the TEXT function.
      I’ll try to guess and offer you the following formula:

      =1910404&TEXT(SEQUENCE(100,1,1,1),"000")

  50. How can i make sequence of number series with one column or row blank.

    1 2 3 4 5 6
    - - - - - -
    7 8 9 10 11
    - - - - - -

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)