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 2. Total comments: 289

  1. hello sir
    i want to make sequence of digits which are with alphabets like ( PLR-101-1) how can i do this please help me out. and if the article is change like ( Thu-508--Set) then how can i make the center digit sequence ?

  2. hi,
    I have three columns : employee, salary (yearly), startdate. I want to generate a provisional monthly cashflow table for the next 3 years with three columns : employee, payment (monthly), paymentdate.
    I know how to this using SEQUENCE for one employee but is there a way to generate it at once for several employees ? In other words, this would be the same as generating sequence lines one below the previous.

    Thanks

  3. Hello, Unfortunately I'm stuck with Excel 2016 and unable to use the SEQUENCE function. Do you have any recommendations for a formula that will create a list of numbers between two values, separated by a certain number? For example, all numbers between 8000 and 10000 seperated by 5. I'll need to do this many times and a formula would be extremely helpful!

    1. Hello! To create a sequence of numbers, you can use the ROW function. The ROW function returns the row number. For example:

      =80000+(ROW(A1)-1)*5

      Copy this formula down the column.

  4. Hi Alexander,

    How to sequence the following please:

    K0151/right
    K0151/left
    K0152/right
    K0152/left
    K0153/right
    K0153/left

    I am not good at it. Thank you for your help!

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

      ="K" & TEXT(CEILING(SEQUENCE(45,1,1,1)/2,1)+150,"0000") & CHOOSE(MOD(SEQUENCE(45,1,1,1),2)+1,"/left","/right")

  5. Hi, How do I sequence with sequence no depends on value on multiple cells/columns, for example:
    Col-A Col-B Col-C Col-D (which is the sequence, which value starts can be random value), Thanks a lot!
    111 RP C 05
    111 RP C 06
    AAA DW R 25
    111 RP C 07
    AAA DW S 55
    AAA DW S 56
    111 RP C 08

    1. Hi! With Excel formulas, you can only make a sequence of numbers. You have text. Also, there is no pattern in your data that can be written as a formula.

  6. I have a question, please help if you can.
    I need a particular pattern in which every number -start from number 1- exist in every successive 5 cells in one column like from A1 to A5 filled with number 1
    From A6 to A10 filled with number 2 and so on.
    How can I do this?
    Thanks in advance

  7. Hi,

    How to convert range (column A1 to B2) into array in single cell for word office 365?

    Example:
    A1=4
    B1=8

    Convert array in single cell like this
    Result: {4;5;6;7;8}

    note:
    Previous version word use below formula but not work on office 365.
    { =ROW(INDIRECT(A1&":"&B1)) }

    1. Hi! Brackets are not needed in this formula: =ROW(INDIRECT(A1&":"&B1))
      In previous versions of Excel, this formula was written as an array formula. In Excel365 you don't need to do this.

  8. Hello,

    I'm after a sequence for creating quote numbers, It would take the Year (2023) Month (04) - then a numerical sequence from 1 down till X.
    From the date the quote was provided.
    Unless the next month starts, then it would be 2023-05-1, etc
    Say Cell Values of 2023-04-1, 2023-04-2, 2023-04-3, 2023-05-1, 2023-05-2, 2023-06-1, 2023-06-2, 2023-06-3, 2023-06-4, 2023-06-5.
    3 Quotes in April, 2023
    2 Quotes in May, 2023
    5 Quotes in June 2023.

    Effectively the counter resets at the start of each month.
    Thanks

      1. Hi Alexander

        Logical Sequence - Quote increments by 1 until such time as the month changes, then it resets back to 1.
        However, the months do not have a set number of quotes, but will be dictated when a new quote opportunity comes in.
        | A | |B|
        2023-05 - 1 (May 2023, Quote # 1) Date is 10th May
        2023-05 - 2 (May 2023, Quote # 2), Date is 15th May.
        2023-06 - 1 (June 2023, Quote #1), Date is 3rd June.

        So the number i would like sequenced is made up of 2 components.

        Part A is a Function of the date that the quote is created. It uses the Year and Month,
        Part B is an incremental step. Starting at 0, it adds an increment of 1 for each new quote created. This continues to increment whilst the Month the quotes are created are the same, when the new month rolls in, the number resets to 0.

        So i see the sequence as an increment of 1 integer, then using a while or IF function, Whilst date of creation is X Month add 1 to integer and retain month/year (Part A). If month now is X+1 (May to June), then B=1 and month now set to X+1.

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

          =TEXT(B2,"yyyy-mm")&"-"&(COUNTIF($A$1:A1,"*"&TEXT(B2,"yyyy-mm")&"*")+1)

          Use the TEXT function to get the year and month from the date as text. The COUNTIF function will count the number of values with that year and month.

  9. Sir maintaining a large number of business transaction data, we use "purchase" for every purchase transaction, "Sale" for every sale transaction and "Payment" for every payment etc. So i want to prepare a Purchase ledger. for this purpose i want to assign a specific reference number to 1st transaction of Purchase that number should be generated in sequence whenever any other purchase related entry posted.
    Example:
    5001 Purchase
    9001 Sale
    5002 purchase
    4001 Sale
    5003 Purchase

    Thanks in advance

    1. Hi! Enter the starting numbers in A1:A2 manually. The formula for A3:

      =COUNTIF($B$1:B2,B3) + INDEX($A$1:A2,MATCH(B3,$B$1:B2,0))

      After that you can copy this formula down along the column.

  10. I want to make a sequence that looks like this, divided by lines and collums, but it can only have 4 digits, from 1 to 1000

    0001 | 0002 | 0003 | 0004 | 0005 | 0006 | 0007 | 0008 | 0009 | 0010 | 0011 | 0012 | 0013 | 0014 | 0015 | 0016 | 0017 | 0018 | 0019 | 0020 | 0021 | 0022 | 0023 | 0024 | 0025 | 0026 | 0027 | 0028 | 0029 | 0030 | 0031 | 0032 | 0033 | 0034 | 0035 | 0036 | 0037 | 0038 | 0039 | 0040 | 0041 | 0042 | 0043 | 0044 | 0045 | 0046 | 0047 | 0048 | 0049 | 0050 | 0051 | 0052 | 0053 | 0054 | 0055 | 0056 | 0057 | 0058 | 0059 | 0060 | 0061 | 0062 | 0063 | 0064 | 0065 | etc

  11. Is it possible to get number as below-

    20220801
    20220901
    20221001
    20221101
    20221201
    20230101

  12. I have a date usage sheet for our inventory where someone inputs the start date, how many days used, then the calculated end date. I have given the user 5 dates to set the start and days used. Is it possible to have the sequence function do all these with one function entry? Is it possible to have multiple starts and stops?

    EX:
    Start Date Days Used End Date
    4/1/2023 21 4/22/2023
    5/1/2023 4 5/5/2023
    5/10/2023 8 5/18/2023
    6/1/2023 10 6/11/2023
    6/15/2023 3 6/18/2023

    Sequence would go 4/1/2023 then fill the dates in until 4/22/2023, 5/1/2023......5/5/2023,5/10/2023.....5/18/2023 and so on.

      1. I think this might work, if I understand what you're after:

        =SEQUENCE(1, + 1,,1)

        You could also calculate the by calculating the number of days between and to make it more dynamic.

        1. Oops...used the wrong formatting in the formula.

          =SEQUENCE(1,[Days Used] + 1, [Start Date],1)

          Hopefully this shows up correctly

  13. HI,
    I have a set of data in one column, say Company Name in A1, PO No-A2,MRN Numbers in A3,A4,A5 etc. How do i bring these datas in different coulmn in sucha a way that All the company name in 1 coulmn, related POs in next column, MRNs in next column. I cannot use transpose since it will arrange all the data horizontally, not in a table manner.

    Thank you

    1. Hi! I don't really understand why you can't use transpose. To help you, give an example of the source data and the desired result.

  14. Hi!

    Tried figuring out through the well done article, but didn't manage to find my way.
    I'm looking for the following to be implemented in a booking sheet I'm working on where certain conditions apply;

    Once data has been entered in a specific cell, it generates a random number in another cell which will function as a reference to that booking.
    The number generated must partly consist of specific values from two given cells, as in the following logic: "data cell 1" - "random number generated" - "data cell 2" .
    The number generated is preferably in sequence which can not be duplicated, ie must respect what is generated in other cells (same column).

    So in other words;

    Once data has been entered in certain cells (column A) then the corresponding cell (column B) generates a random number with the above conditions.

    Hope I was able to convey this understandably enough, and thanks in advance for any help!

  15. How do I sequence this?
    Is there a formula?
    Ex. Wk13 up to WK50
    I want to sequence it and stop doing it manually

    1. Create a sequence of numbers using the SEQUENCE function and combine it with a text string.
      Try this formula:

      ="WK"&SEQUENCE(38,1,13,1)

  16. Hi!
    I have a set of project codes (example format is 90-00480-90GT001-ProjectName 1) in column D. The 90-00480 prefix is entered manually in column A, as is the project name suffix in column C.

    In column B, the 90GT001 part is a sequential number, and I need to ensure that only the next available number in the sequence is used, when a new code needs to be generated. I've tried using the SEQUENCE function in column B and formatted the column to include leading zeros, then concatenated the prefix and suffix using the formula =A2&"-"&"90GT"&B2&"-"&C2, however this results in 90-00480-IT-90GT1-ProjectName 1 (the leading zeros do not pull through so the code format is incorrect).

    How can I fix this?

    Thanks

      1. Absolutely fantastic! Thanks Alex!

  17. Hi, how do I create a sequence based on values from another cell.
    Example: Column "A" has values such as General Admin, Finance, Marketing, etc and I would like column B to generate a sequence such as below please.

    Department Code
    Finance FIN-01
    General Admin GA-01
    Marketing MKT-01
    General Admin GA-02
    General Admin GA-03
    Marketing MKT-02
    Marketing MKT-03
    Marketing MKT-04
    Finance FIN-02

      1. Thank you so much!! it worked.

  18. Hi, how to generate a sequence of say:
    1,1,1 (3 times)
    2,2,2 (3 times)
    3,3,3 (3 times) e.t.c all in one column?

    Meaning

    1
    1
    1 (3 times)

    2
    2
    2 (3 times)

    3
    3
    3 (3 times) e.t.c

    all in one column?

    1. Hi!
      To repeat the same number several times in a sequence, try this formula

      =CEILING(SEQUENCE(50,1,1,1)/3,1)

      1. Thank you so much Sir for your response.

        It works absolutely, your answer really helped, thanks ?

  19. It's possible create a sequences like this 10,-10,20,-20,30,-30 ..... 100,-100 and start again 10,-10,20,-20 ???

    1. Hi,

      I would like to generate a reference number in sequential order every time a new data is added in the next row. Example:

      In the A1: reference is “RED1”.
      If a new data is added in A2 it will automatically generate “RED2” as a reference number. And so on. Which will look like something like this:
      A1: RED1
      A2: RED2
      A3: RED3

      This will be in relation to other data in the table.

      Thanks

    2. by the way, I determined this by:

      1. Copying the series of terms into Wolfram Alpha to identify an equation to produce the series --> a(n) = 5/2(-1)^n(-2n+(-1)^n-1)
      2. Replicating the formula in Excel, using SEQUENCE(20) instead of x --> =2.5*((-1)^SEQUENCE(20))*((-2)*SEQUENCE(20)+(-1)^SEQUENCE(20)-1)
      3. Modifying the formula to create a repeating series using MOD(ULO) function --> SEQUENCE(20) --> (MOD(SEQUENCE(A1)-1,20)+1)

      1. Wow it works!!! thank you so much

    3. Given A1 contains the number of terms you want:

      =2.5*((-1)^(MOD(SEQUENCE(A1)-1,20)+1))*((-2)*(MOD(SEQUENCE(A1)-1,20)+1)+(-1)^(MOD(SEQUENCE(A1)-1,20)+1)-1)

    4. Hi!
      I don't think you can do it with a formula. Just write down those 20 numbers and copy down the column.

  20. Hi, how to make sequence lets say of : 0 21 times, 20 15 times, 40 10 times, 60 6 times, 80 3 times, and 100 1 time; all of it in one row?

    thanks in advance !!!

      1. Hi, how to generate a sequence of say:
        1
        1
        1

        2
        2
        2

        3
        3
        3

        4
        4
        4

        1000
        1000
        1000

        e.t.c all in one column?

  21. Hi

    The below is a part of report, I need to add a numbers 1-7 in the end of the text for every new product description. Can you help please?

    15 Pouch Box (595 * 241 * 125 mm)-1
    15 Pouch Box (595 * 241 * 125 mm)-2
    15 Pouch Box (595 * 241 * 125 mm)-3
    15 Pouch Box (595 * 241 * 125 mm)-4
    15 Pouch Box (595 * 241 * 125 mm)-5
    15 Pouch Box (595 * 241 * 125 mm)-6
    15 Pouch Box (595 * 241 * 125 mm)-7
    4 Slice Clear Plastic Tray (156 Cut)-1
    4 Slice Clear Plastic Tray (156 Cut)-2
    4 Slice Clear Plastic Tray (156 Cut)-3
    4 Slice Clear Plastic Tray (156 Cut)-4
    4 Slice Clear Plastic Tray (156 Cut)-5
    4 Slice Clear Plastic Tray (156 Cut)-6
    4 Slice Clear Plastic Tray (156 Cut)-7
    7 Month Caramel - RSPO SG (M/ SO2)-1
    7 Month Caramel - RSPO SG (M/ SO2)-2
    7 Month Caramel - RSPO SG (M/ SO2)-3
    7 Month Caramel - RSPO SG (M/ SO2)-4
    7 Month Caramel - RSPO SG (M/ SO2)-5
    7 Month Caramel - RSPO SG (M/ SO2)-6
    7 Month Caramel - RSPO SG (M/ SO2)-7

      1. Thank you for quick response, you saved my life:)

  22. Hi!
    I want to generate a random sequence based on previous data.
    E.g., Column A - E has values from 1 to 45

    So if I have 100 rows of such a sequence, is there a formula that can predict/generate the next sequence?

  23. How to predict the series for
    100
    200
    300
    500
    600
    700
    900

  24. Hello,

    I need a formula that would return the sequence for this below:

    Part Number: Sequence: (Formula for this)
    47009 1
    47009 2
    47009 3
    47014 1
    47014 2

      1. Hello,
        There is no pattern as each part number repeats itself differently. I need the sequence to add different descriptions to each one.

  25. Hi
    How do I present such data in a sequence.
    1- 51
    51-101
    101-151

  26. Hi, how do I create a sequence by 1 in a cell that depends on the max number of other cells (H) and if it is selected "NEW" (C) and also depends on the year (F)

    I have this formula: =MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1

    However, a year can have two max numbers which I need to take the sequence from; for instance, 110943000 and 110943700. I would still need the sequences on 110943001, 110943701, 110943002, 110943702, and so on. The formula is only giving me the sequences from 110943700 which is the maximum.

    C18 = NEW
    D18 = 110943000 (ID)
    F18 = IFERROR(VLOOKUP(D18,$D$3:$F$17,3,FALSE),"-") = 9 {this is how the year is calculated), there's no issue with this one}
    H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701 {instead of 110943001}

    it only works in this case:
    C18 = NEW
    D18 = 110943700 (ID)
    F18 = 9
    H18 = MAX(IF($C$3:C17=C18,IF($F$3:F17=F18,$H$3:H17)))+1 = 110943701

    Thank you!

  27. I have a situation in Excel where there is one workbook and it has 6 worksheets in all.First one is a MasterSheet and other
    5 sheets are named as S1,S2,S3,S4 and S5.Now I want to link these 5 five sheets with the Master at various cells in a column
    sequentially.

    Like in S1 E1 shud link with F2 cell in Master,in S2 E1 with F3,in S3 E1 with F4,in S4 E1 with F5 and in S5 E1 in F6 cell in Master.
    How can it be done ?

    Looking forward to hearing from you.

  28. Hi,

    I need to get the following formula to work and I can get it to come up except it's removing the 00 and treating the second last number as a negative and then minus the last number as well I realise this is due to the hyphen/subtraction symbol just wondering if there is a way to bypass this?

    ABC-001-10412-7
    ABC-002-10412-7
    ABC-003-10412-7
    ABC-004-10412-7

    The formula I have used is - ="ABC-"&(SEQUENCE(004,,001)&-10412-7) - excel is removing the 00 after I enter of course and the result is

    ABC-1-10419
    ABC-2-10419
    ABC-3-10419
    ABC-4-10419

    Assistance would be greatly appreciated.

    Thanks

      1. Thank you so much yes it did!!

  29. Hello, I need help with a formula for this... I'm out of ideas..
    A B
    2 = -2000
    (2,5= -1750)
    3 = -1500
    4 = -1000
    5 = -500
    6 = 0
    7 = 500
    8 = 1000
    9 = 1500
    10 = 2000
    Thank you very much for any help.

  30. HI I need to have the data like the following -

    Part1
    Part1
    Part1
    Part1
    Part2
    Part2
    Part2
    Part2

    and so on till 2010 rows and "Part1" should apply to 40 rows and it should increase the counts like Part2 part3 .... till it reaches "Part50" to fill up 2010 rows .

    I believe my query is somewhat similar to your latest reply to a user's query -
    =CEILING(SEQUENCE(300,1,1,1)/3,1)+44858 only it had dates repeated like -
    Oct 25
    Oct 25
    Oct 25
    Oct 26
    Oct 26
    Oct 26

    Please let me know the quickest and most effective approach to accomplish this. Your assistance would be highly appreciated.

  31. Hi,

    I have a data which I need to sequence/group. I actually don't know if its possible (still crossing fingers that it is possible).

    For example, document number 1, 2, 3, 9, 10, 25, 26, 28, 30, 31.

    I need to summarize it to 1-3, 9-10, 25-26, 28, 30-31.

    Is there a way to do it?

  32. Hello,
    I need to create the following sequence

    149597883002568 (360360)
    149597882642208 (360360)
    149597882281848 (360360)
    149597881921488 (360360)
    149597881561128 (360360)
    149597881200768 (360360)
    149597880840408 (360360)
    149597880480048 (360360)
    149597880122691 (357357)
    149597879762331 (360360)
    and so on

    1 to 8 is 360360, 8 to 9 is 357357 and 9 to 10 back again to 360360
    Any advice?

      1. First thanks for answering but I'm sorry, I think I'm not really detail when I explaining
        I need to create sequence that the result is looks like this

        1. 149597883002568
        2. 149597882642208
        3. 149597882281848
        4. 149597881921488
        5. 149597881561128
        6. 149597881200768
        7. 149597880840408
        8. 149597880480048
        9. 149597880122691
        10. 149597879762331

        The first to eight values ​​always increases by 360360, but from eight to nine value, it increases by 357357, nine to ten value is back again to 360360
        And I need to create that sequence more than 100

        1. Hi!
          Sequentially add these numbers to the original number. If the sequence is written in B1, then the formula in C1 can be:

          =$A$1+SUM($B$1:B1)

          Copy the formula down the column.
          How to make a sequence more than 100 - read the instructions for the SEQUENCE function.
          Also note that in your example you're subtracting numbers. In addition, Excel displays such large numbers (more than 15 digits) in exponential format. To see it in full, convert the number to text using the TEXT function.

  33. Hello,

    I'm trying to figure out how to sequence

    a3i5a1p
    a3i5a2p...
    a3i5a9p
    a3i5aap
    a3i5abp...

    then group them in a range of 25. a3i5 and the p never change, and the other two switch between a-z and 0-9. I am working with excel 2013.

    1. If i have raws as following
      1
      2
      3
      5
      6
      7
      10
      12

      I need to write " 1To 3,5 To7, 10,12"

    2. Hello!
      Place 42 characters 0-9 a-z in a separate column. For example, in Z1:Z42.
      To dynamically refer to these symbols, use the INDIRECT function.

      ="a3i5"&INDIRECT("Z"&(CEILING(ROW()/42,1)+10)) & INDIRECT("Z"&(TRUNC(MOD((ROW(A1)-1)/1,42)+1)))&"p"

      For more information, please visit: Creating indirect references from cell values and text.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  34. 1
    Package ID : 15543
    Full Time Senior (5+) Java Web
    Client: Rui Serra (Tendaji)
    Nrupeshkumar Modi [Java Web]
    8-Jun-21
    2
    Package ID : 15570
    Full Time Senior (5+) Java Web
    Client: Mui Mui Seng (Pan United)
    Harshil Modi [Java Web]
    14-Jun-21
    3
    Package ID : 13459
    Full Time Senior (5+) Java Web
    Client: Rui Serra (Tendaji)
    Mayuresh Ratnaparkhi [Java Web]

    Hi , I need to get it into tabular format in excel. In which i need separate column for each filed and subsequent data need to be filled automatically. I tried to put series formula or sequence but its not coming. I am using excel 2016 .

  35. Hello Genius
    I want to create a decreasing series whereby the first and the last letters remain constant while the middle number is decreasing. For example
    MH 42 - A1
    MH 41 - A1
    MH 40 - A1
    MH 39- A1
    This should decrease to zero in that sequence.

    Thanks in advance

      1. Thank you Sir.
        But it's displaying a "#Name Error"

        1. Hi!
          If you have an older version of Excel, try this array formula -

          ="MH "&SORT(CEILING(ROW(A1:A42),1),,-1)&" - A1"

          The SORT function will provide descending order of values.
          Press Ctrl + Shift + Enter so that array function works.

          1. I am using 2021 version.

  36. Hello,
    this is what I need help with please.
    For example, If there is 4 in column X then the subsequent columns need to be filled in with 0,0,0,1,0,0. , and similarly for numbers 1 to 6. Is there a way I can fill in all columns in front of column X that have the 4's in the data set in the same way at once rather than one at a time?

    1. Hello!
      You can use the CHOOSE function to select one of the 6 options.

      =IFERROR(CHOOSE(A1,{1,0,0,0,0,0}, {0,1,0,0,0,0},{0,0,1,0,0,0}, {0,0,0,1,0,0},{0,0,0,0,1,0}, {0,0,0,0,0,1}),"")

      I hope my advice will help you solve your task.

  37. Hi,
    I am looking to insert validation in Excel to enter number in the format that should range from 0000-000-00 to 9999-999-99, and no other format is acceptable.

  38. Hello team!

    I need a solution to increment alphanumeric numbers by 1 that have a string as the prefix, and the suffix as a sequential number. I need the increments to restart from 0 if the suffix string is different.

    For example, in column A, I have sets of different strings, e.g. “ATD” in A2:A7, “FMR” in A8:A11, “OWG” in A12:A15, “ATD” again from A16:A19, etc.

    In column B, I would like a sequence of numbers which includes the string from column A as the prefix and the suffix “-R00”, incremented by one for each instance of that particular string.

    The caveat is that if the string already exists, e.g. “ATD”, I would need the numbering to continue from the last previous instance in column B.

    So in column B, from cell B2, I would have the following:
    2 ATD-R00
    3 ATD-R01
    4 ATD-R02
    5 ATD-R03
    6 ATD-R04
    7 ATD-R05
    8 FMR-R00
    9 FMR-R01
    10 FMR-R02
    11 FMR-R03
    12 OWG-R00
    13 OWG-R01
    14 OWG-R02
    15 OWG-R03
    16 ATD-R06
    17 ATD-R07
    18 ATD-R08
    19 ATD-R09

    I have written the current formula =IF($A2=$A1,CONCATENATE($A2,"-R"&TEXT(ROWS($1:1)-1,"00")),CONCATENATE(A2,"-R00")) in column B, and although this restarts the numbering to R00 for a different string, the next number suffix is not 001 as I require. Could you advise what the best formula is for this?

    Thank you!

      1. Genius, thank you!! I wanted to number through starting at 1, for each category in a different column and this works a treat with amendment to my purpose :)

        =TEXT(COUNTIF($A$1:A1,A1),"00")

      2. Absolutely perfect, thank you for your speedy response, Alexander!

  39. Hi,
    Thanks a lot for this.
    I would like to go in the sequence 001,002,003 etc down a column
    using the sequence function I am getting 001,011,021,031 etc.
    Would appreciate it if you could let me know how to correct this.

    Thanks.

  40. Hi!
    I would like to make a pattern going down a column:

    1
    4
    2
    4
    2
    4
    2
    4
    1

    The first and last value must always be 1, and in-between needs to be a pattern of 4,2,4,2,4... The pattern needs to be autofilled in when I add a value in the column next to it on the same row (it needs to be applied to blank cells so if I add or delete values the pattern remains with 1 at the start and end). I can't figure out what to use so that the first and value stay at 1. Much appreciated if possible to help!

  41. Hello,

    I have a very large table consisting of 5-digit ID codes (approximately 8,000 rows). For each ID code, I need to concatenate or append a separate table consisting of 4-digit product codes (3,000 rows). The final list would be in the neighbourhood of 24m rows (8k x 3k). Is this something I can do in Excel or perhaps Power Query and how?

    tbl_IDCode tbl_ProdCode
    20456 1111
    20457 2333
    20468 2654
    20471

    End Result
    204561111
    204562333
    204562654
    204571111
    204572333
    204572654
    204681111
    204682333
    204682654
    204711111
    204712333
    204712654

    Thank you,
    Dan

  42. Hi I have a question where I need to create a column of index numbers
    beginning at 20 and incrementing by 1 so that the last number in the column is 44 how do I cap it at 44.

  43. hi

    is it possible to sequence
    2222/22
    2223/22
    2224/22 etc

    thanks

  44. 001/22-23
    002/22-23

    HOW TO CONTINUE THIS SEQUENCE ?

  45. Hi Alexander, I am hoping you can help with a formula I have been struggling to find. I need to list all values between two original values. For example, I have the following 2 values: ABC2100701 & ABC2101800. I need a formula to list the entire range between these 2. Is that possible at all?

    1. Hello!
      I believe the following formula will help you solve your task:

      ="ABC210"&TEXT(SEQUENCE(1100,1,701,1),"0000")

      You can learn more about TEXT function in Excel in this article on our blog.

  46. Hello,

    I want to type something in sequence, like below.

    A0101
    A0104
    A0201
    A0204
    A0301
    A0304
    etc.

    Up to
    A7001
    A7004

    Is there a formula doing that typing quickly?

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

      ="A"&TEXT(CEILING(ROW(A1)/2,1),"00") &"0"& (TRUNC(MOD((ROW(L1)-1)/1,2)+1))^2

      Copy this formula down along the column.

  47. Hi - I have a slightly random one. For livestock animals such as cattle, they have passport numbers that go up in a specific sequence, for example:

    UK121629 101653
    UK121629 201654
    UK121629 301655
    UK121629 401656
    UK121629 501657
    UK121629 601658
    UK121629 701659
    UK121629 101660
    UK121629 201661
    UK121629 301662
    UK121629 401663
    UK121629 501664
    UK121629 601665
    UK121629 701666

    There's UK for the country, then the herd number of the farmer (which usually stays the same) and then the last 6 digits are the individual animal's tag number. This goes up by one each time for the last 4 digits, but the first two numbers go up by 10 each time, until they reach 70, then it goes back to 10,20,30 and so on.

    Is there any way I can make this into a sequence?
    Thank you!

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

      ="UK121629 "&TRUNC(MOD((ROW(A1)-1)/1,7)+1)&"0"&ROW(A1653)

      Copy this formula down along the column.

      1. That's great thank you - So next question... If I have a list of unordered passport numbers, is there a way I can order them according to this sequence?

  48. Hi, what is the formula for this kind of sequence?
    20220327.01.01
    20220327.02.01
    20220327.03.01
    20220327.04.01
    20220327.05.01

    I already tried the formula you shared in this article, but I couldn't figure it out. Any help would be appreciated. Thank you!

  49. I am looking to create the ongoing sequence below and could use help with the formula. As I have to go very high with the numbers, I really don't want to be entering them all manually. Thanks!

    2022.1.1
    2022.2.1
    2022.3.1
    2022.4.1
    2022.5.1
    2022.6.1
    2022.7.1

    1. So could I somehow join the rank function with the formula I have above?

      1. Hi!
        If your table uses automatic sorting by columns D, E, F, G, then the rank will be equal to the serial number in the table. H13+1 and so on.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)