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

[megs96]

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
b	2

Table 2

a	1	3
a	1	4
b	2	3
b	2	4

[#2]

@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...