REPELEM Function in Excel

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

Argument	Description	Example
array	An array or range of numbers or text to be processed.	{"A",1;2,"B"}
[m_vert]	(Optional, Default=1) Number of times to repeat the elements vertically. It can be a single number (scalar) or a vector matching the number of rows in the array.	3 or {3;4}
[n_horiz]	(Optional, Default=1) Number of times to repeat the elements horizontally. It can be a scalar or a vector matching the number of columns in the array.	3 or {3,4}

Download the Template

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

✅ Vote on this suggestion

Description

The REPARRAY function repeats (or stacks) the entire array, while REPELEM repeats each individual element of the array. This can be very useful in constructing certain types of matrices, assembling combinations, and as one of the steps to unpivot a table or ungroup by count.

REPELEM function - Repeat Elements of an Array m x n Times

When m_vert is a vector, it should be the same number of rows as the array, with integers that represent the number of times to repeat each corresponding row of the array. Likewise, n_horiz can be a vector specifying the number of times to repeat each corresponding column.

The following image shows REPELEM(items,counts) being used to "Ungroup by Count" which repeats the values in the items column based on the corresponding numbers in the counts column. A blank in counts would be treated the same as a zero.

Another use for REPARRAY and REPELEM is constructing an array defining all possible combinations of rows within two arrays. See COMBINATIONS.

Lambda Formula

This code for using 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 Excel Labs add-in

/**
* Repeat the elements of an array m times vertically and n times horizontally
*/
/*
* Inputs:
*   array: The input array or range whose elements are to be repeated.
*   [m_vert]: (Optional, Default=1) A scalar or vector specifying the number of times to repeat each row vertically.
*   [n_horiz]: (Optional, Default=1) A scalar or vector specifying the number of times to repeat each column horizontally.
*
* Outputs:
*   Returns a new array where each element of the input array is repeated as specified by m_vert and n_horiz.
*
* Notes:
*   - If m_vert or n_horiz is a scalar, all rows or columns are repeated equally.
*   - If m_vert or n_horiz is a vector, the corresponding rows or columns are repeated by the values in the vector.
*/
REPELEM = LAMBDA(array, [m_vert], [n_horiz],
LET(doc, "https://www.vertex42.com/lambda/repelem.html",
    version, "1/13/2025 - Updated commenting",
    // Handle defaults and reshape m_vert to column and n_horiz to row
    m_vert, IF(ISOMITTED(m_vert), 1, IF(COLUMNS(m_vert) > 1, TRANSPOSE(m_vert), m_vert)),
    n_horiz, IF(ISOMITTED(n_horiz), 1, IF(ROWS(n_horiz) > 1, TRANSPOSE(n_horiz), n_horiz)),
    // Get the dimensions of the input array
    rows, ROWS(array),
    cols, COLUMNS(array),
    // Create a vector specifying the number of repetitions for each row, column
    m_array, IF(ROWS(m_vert) > 1, m_vert, SEQUENCE(rows, 1, INDEX(m_vert, 1, 1), 0)),
    n_array, IF(COLUMNS(n_horiz) > 1, n_horiz, SEQUENCE(1, cols, INDEX(n_horiz, 1, 1), 0)),
    // Calculate the total size of the output array
    total_rows, SUM(m_array),
    total_cols, SUM(n_array),
    // Compute cumulative sums for row and column repetitions
    csum_vert, SCAN(0, m_array, LAMBDA(a, b, a + b)),
    csum_horiz, SCAN(0, n_array, LAMBDA(a, b, a + b)),
    // Map each output index to the corresponding input index
    row_indices, XMATCH(SEQUENCE(total_rows), csum_vert, 1),
    col_indices, XMATCH(SEQUENCE(total_cols), csum_horiz, 1),
    // Generate the final array
    MAKEARRAY(total_rows, total_cols, LAMBDA(i, j,
        INDEX(array, INDEX(row_indices, i, 1), INDEX(col_indices, j, 1))
    ))
));

Named Function for Google Sheets

Name: 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(COLUMNS(m_vert)>1,TRANSPOSE(m_vert), IF(ROWS(m_vert)>1,m_vert,IF(ISBLANK(m_vert),1,m_vert))),
  n_horiz, IF(ROWS(n_horiz)>1,TRANSPOSE(n_horiz), IF(COLUMNS(n_horiz)>1,n_horiz,IF(ISBLANK(n_horiz),1,n_horiz))),
  m_array, IF(ROWS(m_vert) > 1, m_vert, SEQUENCE(ROWS(array), 1, INDEX(m_vert,1,1), 0)),
  n_array, IF(COLUMNS(n_horiz) > 1, n_horiz, SEQUENCE(1,COLUMNS(array), INDEX(n_horiz,1,1), 0)),
  total_rows, SUM(m_array),
  total_cols, SUM(n_array),
  csum_vert, SCAN(0, m_array, LAMBDA(a, b, a + b)),
  csum_horiz, SCAN(0, n_array, LAMBDA(a, b, a + b)),
  row_indices, ARRAYFORMULA(XMATCH(SEQUENCE(total_rows),csum_vert,1)),
  col_indices, ARRAYFORMULA(XMATCH(SEQUENCE(total_cols),csum_horiz,1)),
  res,MAKEARRAY(total_rows, total_cols, LAMBDA(i, j,
    INDEX(array, INDEX(row_indices,i,1), INDEX(col_indices,j,1))
  )),
  res
)

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.

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

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

Example 3

Repeat rows and columns independently based on vector inputs.

=LET(
    array, {"A1","B1";"A2","B2"},
    m_vert, {2;1},
    n_horiz, {2,3},
    REPELEM(array,m_vert,n_horiz)
)
Result:
{"A1","A1","B1","B1","B1";
 "A1","A1","B1","B1","B1";
 "A2","A2","B2","B2","B2"}

Example 4: UNGROUP by COUNT (m_vert as a vector)

This example is like the one shown in the image above. Note that if a value in the counts array is blank or zero, the function works, but of course that item will not be included in the resulting array.

=LET(
    items, {"⚂";"⚄";"⚅";"⚀";"⚃";"⚁"},
    counts, {5; 4; 3; 0; 2; 1},
    REPELEM(items,counts,1)
)
Result:
{"⚂";"⚂";"⚂";"⚂";"⚂";"⚄";"⚄";"⚄";"⚄";"⚅";"⚅";"⚅";"⚃";"⚃";"⚁"}

Example 5: Creating a Checkerboard Pattern

This example demonstrates how to use the REPELEM function to create a checkerboard pattern. The base 2x2 matrix alternates between 0 and 1, and is repeated both vertically and horizontally to form the final pattern.

=LET(
    base_array, {0,1;1,0},
    vertical_repeats, 5,
    horizontal_repeats, 5,
    REPELEM(base_array,vertical_repeats,horizontal_repeats)
)
Result:
{0,1,0,1,0,1,0,1,0,1;
 1,0,1,0,1,0,1,0,1,0;
 0,1,0,1,0,1,0,1,0,1;
 1,0,1,0,1,0,1,0,1,0;
 0,1,0,1,0,1,0,1,0,1;
 1,0,1,0,1,0,1,0,1,0;
 0,1,0,1,0,1,0,1,0,1;
 1,0,1,0,1,0,1,0,1,0;
 0,1,0,1,0,1,0,1,0,1;
 1,0,1,0,1,0,1,0,1,0}

Revision History

12/31/2024: Allows m_vert and/or n_horiz to be vectors to specify how many times to repeat each item.
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.

REPELEM