REPARRAY Function in Excel

=REPARRAY(array, [m_vert], [n_horiz])

Argument	Description	Example
array	An array or range of numbers or text	{"A",1;2,"B"}
[m_vert]	(Optional, default=1) Number of times to repeat the array vertically	3
[n_horiz]	(Optional, default=1) Number of times to repeat the array horizontally	2

Download the Template

In the template file, navigate to the Arrays worksheet to see the REPARRAY function in action.

Description

The REPARRAY function in this LAMBDA Library allows you to repeat an array a number of times vertically and/or horizontally. It is similar to the repmat function in Matlab and the tile function in NumPy. REPARRAY works with both numeric and text data.

REPARRAY function - Repeat an Array m x n Times

REPARRAY is useful when you need to repeat an array dynamically (not just once or twice), such as in creating combinations or unpivoting a table.

Algorithm Efficiency!

One way of assembling or "tiling" an array is to build it using REDUCE, such as using VSTACK and HSTACK recursively. However, this is not efficient and is not parallelizable. Luckily, it isn't necessary to use recursion.

The algorithm for the REPARRAY function uses MAKEARRAY and the MOD function. The value of each (i,j) location in the final array can be determined independently. I'm not sure how Microsoft programmed MAKEARRAY, but it should be parallelizable.

To test MAKEARRAY vs. REDUCE for this function, I created a 5x5 array of numbers and used the following TIMER function, gradually increasing the number of vertical repetitions (m_vert).

=TIMER(10, LAMBDA(i, REPARRAY(array,m_vert,10) ))

Efficiency Test for the REPARRAY Algorithm

Notice that the calculation time using the REDUCE method increased exponentially, while the current algorithm using MAKEARRAY increased linearly.

Lambda Formula

This code for using REPARRAY in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

Name: REPARRAY
Comment: Repeat (or tile) an array m times vertically and n times horizontally
Refers To:

=LAMBDA(array,[m_vert],[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/reparray.html",
    m_vert,IF(ISBLANK(m_vert),1,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),1,n_horiz),
    MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j,
        INDEX(array,1+MOD(i-1,ROWS(array)),1+MOD(j-1,COLUMNS(array)))
    ))
))

Named Function for Google Sheets

Name: REPARRAY
Description: Repeat (or tile) an array m times vertically and n times horizontally
Arguments: array, m_vert, n_horiz (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/reparray.html",
    m_vert,IF(ISBLANK(m_vert),1,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),1,n_horiz),
    MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j,
        INDEX(array,1+MOD(i-1,ROWS(array)),1+MOD(j-1,COLUMNS(array)))
    ))
)

REPARRAY Examples

Example 1

Repeat a sequence vector horizontally 3 times.

Test: Copy and Paste this LET function into a cell
=LET(
    array, {1;2;3;4},
    m_vert, 1,
    n_horiz, 3,
    REPARRAY(array,m_vert,n_horiz)
)

Result: {1,1,1;2,2,2;3,3,3;4,4,4}

Example 2

Stack an array vertically 3 times.

Test: Copy and Paste this LET function into a cell
=LET(
    array, {1;2;3},
    m_vert, 3,
    n_horiz, 1,
    REPARRAY(array,m_vert,n_horiz)
)

Result: {1;2;3;1;2;3;1;2;3}

Example 3

REPARRAY can be used to create matrices of constants such as ONES and ZEROS.

Test: Copy and Paste this LET function into a cell
=LET(
    array, 1,
    m_vert, 3,
    n_horiz, 3,
    REPARRAY(array,m_vert,n_horiz)
)

Result: {1,1,1;1,1,1;1,1,1}

Revision History

12/13/2024: Changed defaults to be 1 row or column.

References & Resources

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

REPARRAY - Repeat Array in Excel