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 times and change that 1 cell each time?

Example - I'm looking for an easier way to go from Table 1 to Table 2 without having to manually process it.

Table 1

a

1
b2

Table 2

a13
a14
b23
b24

 

Excel
  • djclements's avatar
    djclements
    Bronze 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