Continuous Order Number in Googel Sheets

The Sequence function in Google Sheets is categorized under the Maths functions (Insert > Function > Maths).

The easiest way to generate an array of sequential number in Google Sheets is by using the Sequence function, not by using the Row function as below.

For example, you can replace the =ArrayFormula(row(A1:A10)) formula with a simple =SEQUENCE(10) formula.

By using Google Sheets Sequence function, you can generate a single as well as multiple column array of sequential numbers. Further, you have the option to provide a step value to control the numbers generated.

For example, if you provide #5 as the step value and start value as #0, the numbering will be like 0, 5, 10, 15, etc.

In addition to this, this Sequence function in Sheets can be used to generate sequential numbers vertically (single column) or horizontally (single row).

Normally we depend on the Row function (single column) or Column function (single row) together with the ArrayFormula for such outputs. Once you learn the Sequence function, you can skip using these combination formulas.

Let's start with the Syntax and Arguments of the Sequence function in Google Sheets.

Syntax:

          SEQUENCE(rows, columns, start, step)        

Arguments:

rows – The number of rows to return.

columns – The number of columns to return.

start – By default the sequential number will start from 1. To start from a different number specify the start number.

step – By default, the number will increase by 1. Specify the number to change the increasing/decreasing.

Formula Examples to the Sequence Function in Google Sheets

Here I have a few formulas to make you understand the use of each argument in this function.

Generate Sequential Numbers in One Column Using the Syntax Sequence(rows) :

          =sequence(10)        

This Sequence formula in Google Sheets will generate a single column array of numbers from 1 to 10. I mean a vertical output.

Generate Sequential Numbers in One Row Using the Syntax Sequence(rows, columns) :

To generate a single row sequential numbers use the function as below.

          =sequence(1,10)        

As you can see I have specified the number of 'rows' as 1 and 'columns' as 10.

To generate sequential numbers in multiple columns and rows, follow the same syntax used in the just above example.

The formula =sequence(2,10) will return the numbers 1 to 10 as above in the first row and 11 to 20 in the second row.

In the below formula I am using all the arguments.

Multi-Column Sequentials:

          =sequence(5,5,10,10)        

Negative Sequential Numbers:

Try the following formula in your Sheet and see the output.

          =sequence(10,1,-1,-1)        

How to Use the Sequence Function in Google Sheets to Generate Sequential Numbering in Reverse Order (Descending Order)?

To return the numbers in descending order you should use the same value in 'rows' and 'start' arguments. In addition to that, the 'step' value should be negative.

          =sequence(10,1,10,-1)        

This formula will return the numbers 10, 9, 8…1.

Dynamic Sequential Numbering in Google Sheets

To generate serial numbers in a column that up to the value in the last row in another column, we can use a Sequence + Match function combination.

First, see this normal formula that generates serial numbers 1-13 in a column.

          =sequence(13)        

Replace this formula with the below dynamic Sequence formula.

          =ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1))        

In this dynamic Sequence formula, the Match formula returns the row number of the last value cell in column B.

I have explained that Match formula here – Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel. It's related to Excel but equally works well in Docs Sheets.

I have subtracted the value 1 from the found number to adjust the header row which included in the count.

To get this numbering in descending order you can use this formula.

          =ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1,1,MATCH(2,1/(B:B<>""),1)-1,-1))        

It is equal to sorting the first dynamic formula in descending order.

          =sort(ArrayFormula(sequence(MATCH(2,1/(B:B<>""),1)-1)),1,0)        

Sequence Function to Generate an Array of Dates

Here is one more advanced tip. You can generate an array of dates using the Sequence function in Google Sheets.

To do that, you must make use of all the arguments in the function. Here is the generic formula to generate a column of sequential dates.

          =sequence(row,column,start_date,step)        

Formula to generate the dates from 01/01/2019 to 15/01/2019 in a column:

          =sequence(15,1,date(2019,1,1),1)        

The output will be in number format. Change that to date from Format > Number > Date.

If the start date is in cell C1, refer to that using Indirect in Sequence.

          =sequence(15,1,indirect("C1"),1)        

hesterpreen1939.blogspot.com

Source: https://infoinspired.com/google-docs/spreadsheet/sequence-function-in-google-sheets/

Share this post