Problem description

Here is an Excel table containing grain crops export information that is grouped by region, product, and week. Below is a part of the table:

We are trying to calculate the increment by week for each product in the same region and enter the result value into column E. The following shows the Quantity increments in same colors through inter-row calculations:

Directions

1.  Open esProc to create a new file.

2.  Write the following code:

A1   Open the source data file and import all data as a table sequence; @b option enables removing extra empty rows.

A2   Rename the 5^th column as Volume and assign values to it because the original column name is long and contains white spaces. Rename it to make the invalid column name convenient to read and calculate.

A3   Add an ID field for recovering the original order as the subsequent grouping operation will shuffle records.

A4   Group A3’s records by product and region.

A5   Calculate quantity increments on each group and assign them to Volume column.

A6   Concatenate groups of records into a large table, sort it by ID to recover to the original order, and get Volume column.

3. Run the program and copy A6’s result to paste it in E2 in Excel.

Q & A Collection

https://stackoverflow.com/questions/63810924/how-can-i-automate-these-formulas