L_REPELEM Function in Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_REPELEM(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 elements vertically	2
n_horiz	Number of times to repeat the elements horizontally	2

Description

The L_REPARRAY function repeats (or stacks) the entire array, while L_REPELEM repeats each individual element of the array. This can be very useful in constructing certain types of matrices.

If n_horiz is blank, then m_vert will be used for n_horiz. For example, L_REPELEM(array,3) is the same as L_REPELEM(array,3,3).

One use for the combination of L_REPARRAY and L_REPELEM is for constructing an array defining all possible combinations of rows within two arrays (see L_COMBINATIONS).

Lambda Formula

This code for using L_REPELEM 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_REPELEM
Comment: Repeat each element of an array m times vertically and n times horizontally.
Refers To:

=LAMBDA(array,m_vert,[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/repelem.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,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
    ))
))

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

/**
* Repeat the elements of an array m times vertically and n times horizontally
*/
L_REPELEM = LAMBDA(array,m_vert,[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/repelem.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,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
    ))
));

Named Function for Google Sheets

Name: L_REPELEM
Description: Repeat each element of 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/repelem.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,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
    ))
)

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_REPELEM Examples

Example 1

Starting with the vector {1;2;3;4}, repeat each element 3 times vertically to get {1;1;1;2;2;2;3;3;3;4;4;4}

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

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

Example 2

Repeat each element of the square array {1,2;3,4} 2 times.

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

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

REPARRAY, COMBINATIONS

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.