HomeGoogle DocsSpreadsheetGet 1-12-123-1234 Patterns Using an Array Formula in Google sheets

Get 1-12-123-1234 Patterns Using an Array Formula in Google sheets

Published on

Is there any particular use of generating 1-12-123-1234 patterns using an array formula in Google sheets?

I think, before giving you the solution, I should answer this question first.

If you know how to generate the said pattern using an array formula, you can transform values in a single column into a database and use them in database functions.

I have recently followed the syntax, transpose(1-12-123-1234_pattern)as a database in DMAX to return Running Max in Google Sheets.

It is simple to get 1-12-123-1234 patterns using a drag-down formula in Google sheets.

Array Formula to Get 1-12-123-1234 Patterns in Google sheets
screenshot # 1

But that may not be useful when you require to use the generated values as an expression in an array formula.

Assume you have values in a few rows in column B. It can be text, date, or numbers.

To understand the pattern, I am just using the sequence from 1 to 10 in cell range B2:B11.

In cell D2, insert this TRANSPOSE-based formula and drag it down.

=transpose({$B$2:B2})

It will return or transform the values into the 1-12-123-1234 patterns in cell range D2:M11.

Array Formula to Generate 1-12-123-1234 Patterns in Google sheets

We can use the below IF and SEQUENCE array formula in cell D2 to get the 1-12-123-1234 patterns in cell range D2:M11 in Google Sheets.

=ArrayFormula(
     if(
        sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),
        transpose(B2:B11),
     )
)

Note 1:- We can replace sequence(10,10)/sequence(10,10) with sequence(10,10)^0.

It’s a logical test as per the following syntax:

IF(logical_expression, value_if_true,,)

Formula Explanation

We can split the above 1-12-123-1234 Patterns formula into three parts.

There are two logical_expression parts and one value_if_true part.

Here are them.

Part 1 (logical_expression)

We have values in 10 rows in B2:B11. So, I’ve used SEQUENCE to generate sequence numbers in 10 rows and columns.

The below is a sequence matrix fill formula.

sequence(10,10)

Note 2:- When you test, enter it as an array formula (Ctrl+Shift+Enter). So it will become =ArrayFormula(sequence(10,10)). It’s also applicable to the other formula parts below.

In the next step, I’ve divided the numbers with the same numbers to make all of them 1.

sequence(10,10)/sequence(10,10)
Sequence Matrix Fill Converted to 1
screenshot # 2

Now we should add sequence numbers 1 to 10 with the above values in each row.

sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)
Logical Expression to Get 1-12-123-1234 Patterns in Google Sheets
screenshot # 3

To add the numbers 1 to 10, we can use sequence(1,10) but what I have used is sequence(1,10,row(A2)-1).

Because I want to add sequence numbers starting from the first row of the range – 1.

The values are in B2:B11. So I want the numbering to start from 1 to 10.

If it’s from B3:B11, I want the numbering from 2 to 11. That’s why I have used row(A2)-1.

The above logical_expression part plays a vital role in getting the 1-12-123-1234 patterns in Google Sheets.

Part 2 (logical_expression)

We will test whether the values in the above output are less than or equal to the corresponding row numbers in each row. That’s part 2.

sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11)

In this, the bold formula is part 2.

Note 3:- We can also use the following formula as the logcial_expression (part 1 and 2 combined).

if(row(B2:B11),sequence(1,10,row(A2)))

Part 3 (value_if_true)

If the above (logical_expression) evaluates TRUE, the formula will return transpose(B2:B11), i.e., value_if_true.

This way, we can use an array formula to generate 1-12-123-1234 patterns in Google Sheets.

Transposed 1-12-123-1234 Patterns

We can get the transposed 1-12-123-1234 patterns in the following way (please refer to the range O2:X11 on screenshot # 1).

Just wrap the above formula with TRANSPOSE.

=ArrayFormula(TRANSPOSE(if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11),)))

It is the type of data that we may sometimes require to solve complex problems using database functions.

At the beginning of this Google Sheets tutorial, I’ve provided the link to one such tutorial.

You may please check that tutorial to understand the usage.

That’s all. Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here