Forum Discussion

megs96's avatar
megs96
Copper Contributor
Aug 06, 2024

How do I paste a data set and have it repeat each piece 10 times?

Hello, I'm working on importing data and for one of my imports, I have to repeat most of the information in a row except for 1 cell. Is there a way to automate this, so I'm not having to paste 10 ti...
excel
djclements's avatar
djclements
Silver Contributor
Aug 06, 2024

megs96 If you have Excel for MS365, you could try the following dynamic array formula:

 

=LET(
    array1, A2:B3,
    array2, D2:D5,
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

 

Adjusted the range references as needed.

 

Sample Results

 

Or, if you simply wanted to repeat the table data n times by row:

 

=LET(
    array, A2:B3, n, 10,
    CHOOSEROWS(array, TOCOL(IF(SEQUENCE(, n), SEQUENCE(ROWS(array)))))
)

 

See attached...

Resources