Macro to copy rows to sheet2 based on value in row

Copper Contributor



Hoping someone can help with a formula or macro to copy data in rows to sheet2 based on the value in the row (column F). Column F does not need to come over to sheet 2 but the other column headings should.  Using the example below, row 2 would duplicate 17 times and row 3, 106 times.

I am using Excel365



Thanks in advance!


2 Replies


Based on How to repeat values X times in excel | LAMBDA: enter the following formula in A2 on Sheet2


=LAMBDA(value, repeat, CHOOSEROWS(value, XMATCH(SEQUENCE(SUM(repeat)), SCAN(, repeat, LAMBDA(a, b, a+b)), 1)))(Sheet1!A2:E8, Sheet1!F2:F8)


Adjust the ranges A2:E8 and F2:F8 as needed.

That does it! Thank you so much!