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.

Q & A Collection

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