Excel or Google Sheets—Is there a way to duplicate rows in place using only formulas?

Question

Using only excel/google sheets formulas, I would like to take a table like this:

a b c
q r s
x y z

and turn it into something like this:

a b c
a b c
a b c
q r s
q r s
q r s
x y z
x y z
x y z

The point is that the rows are duplicated n times but maintain the sort order of the original table.

Asking for both is too broad. Please choose one and please include what you have tried. — Scott Craner, Oct 20 '22 at 22:29
Alternatively: `=MAKEARRAY(COUNTA(A:A)*3,3,LAMBDA(r,c,INDEX(A:C,ROUNDUP(r/3),c)))` — JvdV, Oct 21 '22 at 07:47
I completely agree with the close decision except that the previous answers were Google sheets only, for 1D arrays not 2D arrays, and didn't include any Lambdas. — Tom Sharpe, Oct 21 '22 at 12:05

player0 · Accepted Answer · 2022-10-21T11:10:14.330

1

use in google sheets:

=LAMBDA(y, z, INDEX(SPLIT(FLATTEN(TEXT(BYROW(y, LAMBDA(x, 
 TEXTJOIN("",,x))), IFERROR(SEQUENCE(1, z)/0, "@"))), "")))
 (A1:C3, 3)

or try:

=LAMBDA(x, y, REDUCE(x, SEQUENCE(y-1), 
 LAMBDA(a, b, IF(b, {a; x}))))
 (A1:C3, 3)

edited Oct 21 '22 at 11:10

answered Oct 20 '22 at 22:32

player0

Man you're getting better with `REDUCE`, could you be more slower?O.O – Osm Oct 20 '22 at 22:40
1

Wow—thank you so much! The top suggestion preserves the order the way I wanted . The shorter lambda function did duplicate the rows, but didn't group the same ones together. – c0rb1n Oct 20 '22 at 22:50
With 0 as the jersey number :D @player0 – Osm Oct 20 '22 at 23:02
2

I really like the nesting with `REDUCE()` in `LAMBDA()`. I did try this and it's not stacking correctly. I used `=LAMBDA(x;y;MAKEARRAY(ROWS(x)*y;COLUMNS(X);LAMBDA(r;c;INDEX(x;ROUNDUP(r/y);c))))(A1:C3;3)` to fix that but it's essentially the same technique. Well done! Good alternative to the missing `LET()` function. – JvdV Oct 21 '22 at 07:51
@JvdV Your function allows blank cells to be included in the data being duplicated, which is nice. Thanks for the comment! – c0rb1n Oct 21 '22 at 11:07

1 Answers1