Excel Copy Each Row Multiple Times by the Value of a Specific Column
Problem description
The following product quantity table is stored in an Excel file. The first column contains product IDs and the second column contains the remaining quantity of each product. The following are the initial values:
Item ID |
Qty Remaining |
Invoice No |
00001 |
2 |
40404 |
00002 |
9 |
50505 |
00003 |
0 |
60606 |
According to the remaining quantity of each product (Qty Remaining), we are trying to repeat each Item ID value for n times. Except for the first row, which is the original row, the other rows only retain the Item ID value. Besides, as the Qty Remaining value for 00003 is 0, it will be excluded from the new table. Below is the desired result:
Item ID |
Qty Remaining |
Invoice No |
00001 |
2 |
40404 |
00001 |
||
00002 |
9 |
50505 |
00002 |
||
00002 |
||
00002 |
||
00002 |
||
00002 |
||
00002 |
||
00002 |
||
00002 |
Solution
Use SPL XLL plug-in
Write the formula in a blank cell:
=spl("=E(?).news(#2;'Item ID',if(#==1,'Qty Remaining',null):'Qty Remaining',if(#==1,'Invoice No',null):'Invoice No')",A1:C4)
As shown:
Return:
Explanation:
Repeat each record for n times according to the quantity recorded in the second column to generate a new table sequence. The if expression is used to retrieve values for the second and third columns, where the original values are retained for the first row and null values are used for the other rows.
https://stackoverflow.com/questions/63711340/excel-return-cells-value-based-upon-another-cell
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/