L_REPARRAY Function in Excel

=L_REPARRAY(array, m_vert, n_horiz)

Argument	Description	Example
array	An array or range of numbers or text	2
m_vert	Number of times to repeat the array vertically	2
n_horiz	Number of times to repeat the array horizontally	2

Download the Template

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

Description

The VSTACK and HSTACK functions in Excel are extremely useful for assembling arrays, however, I have frequently needed the ability to repeat an array a number of times vertically and/or horizontally. In MATLAB this can be accomplished with the repmat function. In NumPy, you could use the tile function.

The L_REPARRAY function in this LAMBDA Library now allows you to repeat an array a number of times vertically and/or horizontally, using a syntax similar to the Matlab repmat function. L_REPARRAY works with both numeric and text data.

Lambda Formula

This code for using L_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: L_REPARRAY
Comment: Repeat 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),n_horiz,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),m_vert,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)))
    ))
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Repeat an array m times vertically and n times horizontally
*/
L_REPARRAY = LAMBDA(array,m_vert,[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/reparray.html",
    m_vert,IF(ISBLANK(m_vert),n_horiz,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),m_vert,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: L_REPARRAY
Description: Repeat 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),n_horiz,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),m_vert,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)))
    ))
)

Warning

These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

L_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,
    L_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,
    L_REPARRAY(array,m_vert,n_horiz)
)

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

Example 3

L_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,
    L_REPARRAY(array,m_vert,n_horiz)
)

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

L_REPARRAY