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:

Directions

1.   Open esProc and create a new file.

2.    Write the following code:

A1  Copy data area A1:C4 in the Excel file, paste it onto the clipboard, and import data from the clipboard as a table sequence. Note that values in the first column should be imported as string type. So, we need to specify data type for this column in advance, otherwise the column will be parsed as numbers.

A2   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.

3.  Copy headers in the first row to Sheet2 in the Excel while set data type for the first column as text, otherwise the data pasted in will be parsed as numbers by Excel. After code is executed, copy the content of cell A2 and paste it to cell A2 on Sheet2 in Excel.

Q & A Collection

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