How to Get the Minimum Value in a Specific Column Among a Continuous Set of Rows with Same Values in Another Column

Problem description & analysis

Below is Excel file book1.xlsx:

undefined

We are trying to locate continuous records where column B values are setup, find the smallest column A value in this group of records, and enter the target value into column C in the first record of this group. The desired result is as follows:

undefined

Solution

Write the following p1.dfx in esProc:

A

1

=clipboard().import@t()

2

=A1.group@o(B).select(~.B).run(~(1).C=~.min(A))

Explanation

A1   Import data from the clipboard, during which @t option enables reading the first row as column headers.

A2  In the original file, column B contains only setup values and empty values. As the task requires getting a grouping records where column B contains continuous setup values, an order-based grouping is needed. After the grouping, in each eligible group, get the smallest column A value and assign it to column C in the first record.

After the program is executed, select cell A1 in esProc, and click Copy data on the right. Then back to Excel to click cell A2 and press Ctrl+V to paste the result in.

Q & A Collection

https://stackoverflow.com/questions/63790137/excel-lowest-value-in-column-a-while-column-b-is-not-blank