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: 437

  1. Hello sir plz solve this problem in excel formula

    1
    1
    1
    1
    2
    2
    2
    2
    3
    3
    3
    3

    is there a way to do this in excel? thanks

    1. Hello! To create a sequence of repeating numbers, use the CEILING function along with SEQUENCE and specify the number of repeats in the formula. Please try the following formula:

      =CEILING(SEQUENCE(20,1,1,1)/4,1)

  2. 00-00-98
    00-00-99
    00-01-00

    can i use sequence in this?

  3. Hello

    is there a formula to create numbers in cells for instance put 1 in column A and then in Column B it would put 1-2, then skip a number and put 3-4,6-7,9-10,12-13. trying to set up for custom print pages. and another code to skip 2 pages for instance 1-2,5-6,9-10,13-14,17-18. but would be put in column A 1 and in cell A2 put a 2 there and it would go in sequence like that.

    Thank you
    Kenny

    1. Hi! Please re-check the article above since it covers your case. Use the SEQUENCE function to create a sequence of numbers. For example:

      =SEQUENCE(10,1,1,4)

  4. Hello sir plz solve this problem in excel formula

    As a like number one coulum and wants to be A 4789 second number 792
    Etc every cells drag the formula but automatic every number combine .
    A=4
    A=7
    A 8
    A 9
    B 7
    B 9
    B2
    C 6
    C3
    C9
    C2
    E 7
    E 8
    G 1
    G 4

    1. To extract each symbol from the text into a separate cell, you can use the MID function. For example:

      =MID(A1,ROW(A1:A20),1)

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  5. Hi!
    Looking for a formula for a sequence (seems basic but somehow have not been able to figure it out):

    24-1
    24-2
    24-3

    The "24" as in the year, so for next years it would need to be 25, and numbers always start at 1 and could go anywhere from 80 to 150 to end (used to number closed files).

    Any guidance is greatly appreciated.

    1. Hi! To repeat 100 times the number 24 and then 100 times the number 25, use this formula:

      =CEILING(SEQUENCE(200,1,1,1)/100,1)+23

      Combine sequences of numbers and text using the CONCATENATE function. For example:

      =CONCATENATE(CEILING(SEQUENCE(200,1,1,1)/100,1)+23,"-",SEQUENCE(200,1,1,1))

      1. Thank you so much for your guidance. After trying your formulas and getting the #SPILL! message, I realized that it wasn't going to work on an Excel table, and had to convert it to range (it was the only way the formulas would work, I just don't understand why?).

  6. Hi,
    I'm after some help. I want to create a project reference number in excel The first will read GBP1000M-24 This will be GBP1000 03/24, the next one would read GBP100103/24 or to could be GBP100104/24 and so on....

    Many thanks

    1. Hi! Your 4 examples are not a sequence of numbers. If you see a pattern in them, describe it. I suggest you study the above article and comments. You will find many examples. Or describe your problem in detail.

  7. This would be really usual if you could spill into a different column. So if you had a list of 60 names in column B and you wanted to do those in three sets of 20, yould need the spill area to be Column A (Name in B), Column C (Name in D) and Column E (Name in E).

    1. Sorry, I have no idea exactly what the task is. To understand what you want to do, give an example of the source data and the expected result.

  8. Hi Alexander,

    I'm trying to implement a sequence in a set of (same sized) merged cells.
    For this i'm getting a #spill! error.

    Is there any way of implementing a sequence function in a set of merged cells?

    Thanks in advance!

  9. Hi I need to make a number sequence for 500 items.

    1000-7300
    1001-7300
    1002-7300

    is there a way to do this in excel?

  10. Hi, how do I create a sequence just like in my sample
    50-34386-1582 S. 2000

  11. Bonsoir,
    comment créer cette séquence :
    LS-001/24
    LS-002/24
    LS-003/24
    ...
    ....
    ...
    LS-500/24

  12. Hi! I'd like to create rows that has a pattern like this...

    00001-00010
    00011-00020
    00021-00030
    00031-00040

    up to 15990-16000

    I was wondering which formula to use. Thnak you!

      1. Thank you for your help!

  13. Need sequence like below: could you please help me.

    1 01-02-2024 S1
    2 01-02-2024 S2
    3 01-02-2024 S3
    4 02-02-2024 S1
    5 02-02-2024 S2
    6 02-02-2024 S3
    7 03-02-2024 S1
    8 03-02-2024 S2
    9 03-02-2024 S3

    1. Hi! As I wrote earlier in the comments, you can create a sequence of repeating numbers or dates using the CEILING + SEQUENCE function.
      To create a repeating sequence of numbers from 1 to 3, use the MOD and TRUNC functions.
      Try these formulas:

      =DATE(2024,2,1)+CEILING(SEQUENCE(100,1,1,1)/3,1)-1
      ="S"&TRUNC(MOD((SEQUENCE(100,1,1,1)-1)/1,3)+1)

      I hope this will help.

  14. is there any logic to create a conditional sequence as shown below?

    Sl.No. Ref
    1.1 aa
    1.2 aa
    1.3 aa
    2.1 ab
    2.2 ab
    3.1 abc
    3.2 abc
    3.3 abc
    3.4 abc

    1. Hi! If there is some logical sequence in your data, describe it. Then I can understand what you want to do. Also note that you can create a sequence of digits using the SEQUENCE function.

      1. mentioned is the sample of logical sequence.

        Sl.No. Band Product
        1.1 Dell Laptop
        1.2 Dell Monitor
        1.3 Dell NUC
        2.1 Lenovo Laptop
        2.2 Lenovo Tablet
        3.1 Apple i-Phone
        3.2 Apple i-Pad
        3.3 Apple i-Mac
        3.4 Apple i-Watch

  15. I'm using the SEQUENCE function to make a dynamic calendar that is auto populated by the year input. My formula is =SEQUENCE(1,7,DATE(C2,1,1)-WEEKDAY(DATE(C2,1,1))+1,1). It works out great however I would like the sequence to skip every other row so I have room to add items to my calendar. How can I do this?

    1. Hi! Based on your description, it is hard to completely understand your task. To understand what you want to do, tell me what is written in cell C2 and give an example of the desired result.

      1. i believe he is looking for

        a1 15 jan
        a2
        a3 22 Jan
        a4
        a5 29 Jan

        there is an empty line after each auto filled seq line, so we can fill in information/ make notes like how we have on paper calendar

        1. Hi! Specify the starting date using the DATE function. Using the formula CEILING(SEQUENCE(50,1,0,1)/2,1)*7, add the numbers 0 7 7 14 14 14 21 21 21 and so on consecutively to this date. This formula will generate a sequence of 50 numbers, starting from 0. The CEILING function rounds each number in the sequence up to the nearest integer that is divisible by 1. This effectively rounds up each number to the nearest whole number. The result is then multiplied by 7.
          The formula (IF(MOD(SEQUENCE(50),2)=0,0,1)) will generate a sequence of numbers 0 1 0 1 0 1 0 1 0 1 0 1 and so on in the cells. The SEQUENCE function generates a sequence of numbers from 1 to 50, and the MOD function checks if each number in the sequence is even or odd. If the number is even, the formula returns 0, and if the number is odd, the formula returns 1.

          =(DATE(2024,1,15) + CEILING(SEQUENCE(50,1,0,1)/2,1)*7) * (IF(MOD(SEQUENCE(50),2)=0,0,1))

          To show dates in the format you want and not show zeros, set a custom date format in the cells: dd mmm;;

          1. Thank you for your help. It is greatly appreciated!

  16. Hi there, I'm attempting to generate a sequence where the number of rows is dependent on Column A, with only one Column, starting with 0, and increment step is 1.

    Column B has an identifier that repeats for every like row. For example, in the case of multiple Contact records under one Company, the Company in this case would repeat in Column B, where each Contact Record would be unique in Column A.

    The sequence I want to apply would start over from 0 once Column B no longer repeats. The output would be in Column C. Please see the example table below of my desired outcome.

    Contact Company Sequence ID
    5991 416 0
    4565 416 1
    1790 416 2
    1761 416 3
    1023 416 4
    6924 417 0
    4770 417 1
    4640 417 2
    1309 418 0
    7773 418 1
    2682 419 0
    7898 419 1
    8310 419 2
    1209 419 3
    1314 419 4

    How do I achieve the output in Column C?

  17. 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 ?

  18. Hi.
    I want to create a dynamic numbering for some kind of Database for Samples.
    I got an Input Table where i write the basic informations in. Then i write the total Number of Objects.
    Object1 4
    Object2 3

    Now i want to create a Dynamich numbering in a list where i have following collumns

    Intern ID Position Date Sample Number Samples Name

    after the 4th Sample for Object 1 it should start with the First Sample of Object 2

    1. Hi! Please clarify your specific problem or provide additional information to understand what you need.

  19. I have 3 columns of numbers data . The device count changes, The AMP and channel is always the same. I was trying to see if a =sequence function would work but not figuring it out if so.

    AMP Channel Device Count
    1 1 20
    1 2 1
    1 3 10
    1 4 5
    1 5 2
    1 6 3
    1 7 7
    1 8 9
    2 1
    2 2
    2 3
    2 4

    I need to create a column that looks like
    1-1-1
    1-1-2
    to
    1-1-20
    1-2-1
    1-3-1
    to
    1-3-10
    ect

    Any help would be great thanks.

      1. Thanks for that help, Is there a way to get it then to continue and the second number change after 20 entries, to 1-2-1? I worded it wrong maybe above.

        1. Hi! I advised you to read the comments above. Too bad you didn't. To repeat each number of the sequence 20 times, use the formula:

          =CEILING(SEQUENCE(80,1,1,1)/20,1)

          To repeat the sequence several times, use the formula

          TRUNC(MOD((SEQUENCE(80,1,1,1)-1)/1,20)+1)

          Concatenate into a text string using & operator:

          ="1-"&CEILING(SEQUENCE(80,1,1,1)/20,1)&"-"&TRUNC(MOD((SEQUENCE(80,1,1,1)-1)/1,20)+1)

  20. How to generate consecutive numbers starting from 43 to 60 and each number should repeat 5 times.

    For example

    column A
    43
    43
    43
    43
    43
    44
    44
    44
    44
    44
    45
    45
    45
    45
    45
    and
    upto 60 ?

  21. Hello,
    I want to create a reference number formula in an excel cell in a payment voucher such that when I save one payment voucher and I close and open the payment voucher again, It will increment itself.

    Thank you.

    1. Hi! Unfortunately, without seeing your data, it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  22. Hello

    I'm attempting to link two different spreadsheets, one being a view/ work-log, the other is an entry-log. The work-logs only purpose is to calculate the remaining dollar amount and percentages. The entry-log is an ongoing form that totals around 50 individual sheets, which means every new entry will differ. The entry-log will only use 7-12 cells that reflect on the work-log in sequential order. The problem I'm having is how to add a traspose fx that will work in sequence with the two forms to prevent me from having to link them manually for each sheet?

    Is it possible to add a sequence with this formula below without removing the transpose fx?

    =TRANSPOSE('[Weatherization - Joshen RFP.xlsx]JOSHEN '!$C$14:$C$17)

    1. Hello! It is very difficult to understand a formula that contains unique references to your data, which I don't have. Unfortunately, I can't understand what you want to do.

  23. 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

  24. Hi,
    I have CSV data that has been loaded into Excel using the common method under the Data / From Text menu, into a separate worksheet labelled "Raw Data". I want to paste this data into another worksheet from a start position identified as "1". The simple formula used to achieve the "1" starting position is =IF(CE$4=BJ695,1,""), where CE$4 is time in DD/MM/YYYY hh:mm:ss format and BJ695 is one of a series of cells with the same time format. The time is in sequential order in column BJ. The formula identified places a "1" as the starting position in column "CE". In the adjacent column "CD" the data from the worksheet "Raw Data" should start. I have tried Index Match formulae to no avail and am losing my mind over this. Is there a simple way of completing this task without introducing many more helping columns, as the worksheet is already large? I would greatly appreciate any help provided.
    Many thanks... Damian.

  25. Can I use the sequence function if I want to repeat number 12 times then incrementing 1 until I reached 24 it goes back to number 1? Thanks

    1. Hi , I want to apply a function on a generated sequence. Say there are 5 numbers {1,2,3,4,5} , I want harmonic sum of each the numbers in the sequence and then I want cumulative sum of the results. How will it be possible ?

    2. I don't think this can be done with a sequence function. Write down 24 numbers and copy them as many times as necessary.

  26. Hi ,

    Seek your guidance how shall i proceed built the sequence formula for below scenario in excel template
    1.scenario 1
    13/9/ABC/01
    13/9/ABC/02
    13/9/ABC/03
    13/9/ABCa/04
    13/9/ABC/05

    13/9 > referring to DD/MM base on today/weekday case ID receive
    ABC - defaulted
    01 - refer to sequence number for each entry of the row

    2. Scenario 2 sequence format
    202309120001
    202309120002
    202309120003
    202309120004
    "20230912" - referring to current request date receive for the data entry
    "0001", 002,003 - referring to sequence number of request received for the day
    if the subsequent date no request, then will no case ID generate
    Assume case receive on 20230915, then the case ID number will be 2020309150001.....follow by 202309150002...etc

    Thank you.

      1. thank you so much on your useful tips.
        The serial number working perfectly for Date 9 Oct 2023. However, when is new date eg: 10 Oct 2023, 11 Oct 2023, the sequence number is not reset start with "0001". Expected format shall be 10/10 ABC/0001, 10/10 ABC/0002.....etc if there is new date 11 Oct 2023 then shall be 11/11 ABC/0001, 11/11 ABC/0002....etc. Can this be done with formula?
        9-Oct-23 09/10 ABC/0001
        9-Oct-23 09/10 ABC/0002
        9-Oct-23 09/10 ABC/0003
        9-Oct-23 09/10 ABC/0004
        9-Oct-23 09/10 ABC/0005
        10-Oct-23 10/10 ABC/0006
        10-Oct-23 10/10 ABC/0007
        12-Oct-23 12/10 ABC/0008
        13-Oct-23 13/10 ABC/0009

        Thank you.

        1. Hi! Based on your second description, it is not about creating a sequence. You need to create code for each new record. You can use the COUNTIF formula for dates to determine the ordinal number of the record within each day. Unfortunately, without seeing your data, it is impossible to offer you a formula.

  27. I need to create a sequence that consists of two columns, the first being Serial No., which contains sequence 1 to 100,000. Columns. 2 is having a code that will have a format based on the serial number value from column 1, i.e., 1 to 1000, code value will be 1, column 1001-2000 column 2 value is 2, which means after each 1000 the code value will change. Can you please help find a simple way to do that?

    1. Hi! You can find the formula for the first column in the article above. For the second column, you can use the rounding to thousands formula:

      =ROUNDUP(A1/1000,0)

      Or you can use the sequence formula, as has been described many times in the comments to this article:

      =CEILING(SEQUENCE(100000,1,1,1)/1000,1)

  28. How wil autogenerate responses for 200 respondents, already have for 55 responses in excel

  29. i have a set of responses from 55 respondents, but i need like 200 ,how will i autogenerate the remaining responses .

  30. Is it possible
    300/7/2023
    301/7/2023
    302/7/2023
    303/7/2023

    1. Hi! Create a sequence as described in the article above and add a text string using the & operator.

      =SEQUENCE(10,1,300,1)&"/7/2023"

  31. Hello, I have these names in A1:A8 and need to enumerate them on C1:C8, excluding the ones that say treasury. The sequence also depends on the year selected in B1:B8
    A B
    1 Other property 2023
    2 Passenger vehicle 2023
    3 Non-passenger vehicle 2023
    4 Treasury 2023
    5 Other Property 2023
    6 Non-passenger vehicle 2024
    7 Treasury 2024
    8 Non-passenger vehicle 2024

    Expected sequence in C
    1 01
    2 02
    3 03
    4
    5 04
    6 01
    7
    8 02

    1. Hi! The expected sequence you described is not very clear to me. Also note that you will be able to use the year if it is written in a separate column as a number.

  32. 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.

  33. 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")

  34. 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.

  35. 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

  36. 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.

  37. 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.

  38. 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.

  39. I want to create an excel sheet that records dates for maintenance of an equipment but for every time we update the date it should count so we know how many times we have done the maintenance. What can I do? If there are any equations to be written please assist.

  40. 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

  41. Hi, Could you help with the below:

    We need to have a cell to enter the number, example 10. So, here 10 refers to the number of classes and we should get the same number of rows/columns to be added automatically to input the date & time for each of the class.

    If I input 5 in the target cell. Then it should create 5 rows/columns for each date & time. The date & time should be in sequence.

    Many thanks in advance

    1. Hi! Your task is not completely clear to me. To recommend a formula to you, show an example of the result you want to get.

  42. Is it possible to get number as below-

    20220801
    20220901
    20221001
    20221101
    20221201
    20230101

  43. 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

  44. 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.

  45. 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!

  46. 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)

  47. 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!

  48. 1st row: 2000 0 0 2000 0 0 and so on over a month period. How to do it with a click?

    Next step:
    If i have multiple rows.. each has a different sequence of numbers.. how to fill a sheet over a month period (30 columns)?

  49. 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.

  50. 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 👍

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 :)