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

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.