Copy each row N times (N is the cell value of the specified column)
In the Excel table below, each row is a product, and the 3rd column is quantity of the product.
A |
B |
C |
D |
E |
F |
|
1 |
F |
Designation |
Quantity |
Length-raw |
Width-raw |
Thickness-raw |
2 |
A |
4LLW |
2 |
2795 |
1250 |
18 |
3 |
B |
16LLWW |
3 |
2700 |
90 |
18 |
4 |
C |
1LLW |
4 |
2500 |
1250 |
18 |
5 |
D |
5LLW |
2 |
2500 |
1250 |
18 |
6 |
E |
2LLW |
5 |
2500 |
1100 |
18 |
7 |
F |
6LLWW |
4 |
2500 |
450 |
18 |
8 |
G |
DeskFront2 LLWW |
1 |
2414 |
1164 |
18 |
9 |
H |
Desk Shelf 1LLWW |
1 |
2414 |
300 |
18 |
Task: Copy each row N times (N is the cell value of the 3rd column) and display values in the original 3rd column as empty. The expected result is as follows:
A |
B |
C |
D |
E |
F |
|
11 |
F |
Designation |
Quantity |
Length-raw |
Width-raw |
Thickness-raw |
12 |
A |
4LLW |
2795 |
1250 |
18 |
|
13 |
A |
4LLW |
2795 |
1250 |
18 |
|
14 |
B |
16LLWW |
2700 |
90 |
18 |
|
15 |
B |
16LLWW |
2700 |
90 |
18 |
|
16 |
B |
16LLWW |
2700 |
90 |
18 |
|
17 |
C |
1LLW |
2500 |
1250 |
18 |
|
18 |
C |
1LLW |
2500 |
1250 |
18 |
|
19 |
C |
1LLW |
2500 |
1250 |
18 |
|
20 |
C |
1LLW |
2500 |
1250 |
18 |
|
21 |
D |
5LLW |
2500 |
1250 |
18 |
|
22 |
D |
5LLW |
2500 |
1250 |
18 |
Use SPL XLL to do this:
=spl("=?.conj(~3 * [~]).run(~3=null)",A2:F9)
conj()function concatenates members of a sequence. run() function modifies a sequence. "Integer N* a sequence" means copying members of a sequence N times.
Source:https://www.reddit.com/r/excel/comments/1cjhvzx/what_formula_to_create_multiple_copies_of_a_row/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
