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:

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:

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.

Q & A Collection

https://stackoverflow.com/questions/63711340/excel-return-cells-value-based-upon-another-cell