#142 — Expand One Row into Multiple Rows Based on Value

Problem description & analysis:

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:

Task: 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:

Solution:

Use SPL XLL and enter the following code:

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

Code explanation: 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.

---

Download esProc Desktop for FREE and boost your workflow efficiency today with SPL XLL!!! 🚀✨⬇️

SPL download address: esProc Desktop FREE Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

YouTube FREE courses: SPL Programming