There is a data table, which stores some information of products. The first column is the serial number, and the second column is the remaining quantity. The initial data is as follows:

Now we want to copy each value in column ItemID n times according to the remaining quantity of product (i.e., the number in Qty Remaining column). The requirements are: 1)Except for the first row (as the original row), the other copied rows only retain the value in column ItemID; 2) The remaining quantity of product 00003 is 0, this row will be no longer retained in the new table. The expected result is as follows:

Script:

=spl("=E(?1).news(#2;'ItemID',if(#==1,'QtyRemaining',null):'QtyRemaining',if(#==1,'InvoiceNo',null):'InvoiceNo')",A1:C4)

Copy each row n times according to the value in the second column to generate a new table sequence, where the values in the second and third columns use the if expression. If it is the first row, take the original value, otherwise take the null value.
 

esProc Desktop and Excel Processing
14.2 Generate continuous array - concatenate results into a string
14.4 Expand one row into multiple rows after splitting text