Problem description & analysis

The following is data in Excel file book1.xlsx:

Column D is the first level grouping field. Column B is the second level grouping field. We are trying to add a new column result, where a value is entered only to the first row in each second level group while other the other rows are left empty. The rule is like this: If a second level group contains more than 3 records, assign 3 to result in the first record; if one contains 3 or fewer records, assign the count result to it. Below is the expected result:

Solution

We write the following script (p1.dfx) in esProc:

Explanation:

A1  Import data in the clipboard as a table sequence.

A2  Group A1’s table sequence by column B, count records in each group, and assign 3 to result column in the first row in the group if the count is greater than 3, and the real count result to the first result if it is 3 or fewer.

After the program is executed, select cell A1 in esProc and click the corresponding “Copy data” button on the right. Back to Excel to click cell A2 and press Ctrl+V to paste result in.

Q & A Collection

https://stackoverflow.com/questions/64223882/count-double-entries-in-2-rows-no-more-than-3-times