Intra-group Sorting after Grouping

Below is a list of closing prices of a stock in a certain month, where data is grouped by week and the daily stock price increment is calculated. Now sort the weekly records by closing price in descending order for easy viewing.

Excel

Copy the results from column E, and paste them in column F by performing the Paste Special action:

There are two ways to sort the weekly stock data by daily increment:

Method 1: In Excel, to sort the weekly stock price data by daily increment in descending order, you can only do the sorting in each week group according to the stock price increment in column E. The result is as follows:

In Excel, error occurs after sorting because the cross-row cell references in the formulas will not change along with the positions of formerly referenced cells, but will simply locate positions according to the offset between the referenced cell and the current cell. Therefore, you will have to eliminate the cells calculated with wrong formulas and keep the right data such as the daily increment values in column F.

To sort each group of the grouped data, you will have to operate on each group one by one. When the data volume is huge and the number of groups is relatively great, the job will become cumbersome and error-prone. In addition, if there are formulas in the cells before sorting, then you must keep the numeric values to protect them against the error resulted from the wrong references by the formulas during sorting.

Method 2: Sort data in ascending order by Week, the primary sort field, and in descending order by column F, the secondary sort field. Then, group data by week over again. The result is the same as that obtained through Method 1.

This method achieves a relatively high efficiency when the data volume is great since it saves you from the action of sorting in each group one after another. However, you still need to copy the results in the cells with formulas, and the aggregate data in the grouping rows may be destroyed during ungrouping and regrouping.

In Excel, records must be sorted before grouping. If you need to perform sort again in each group, then the cumbersome operations will easily give rise to errors. The more data or levels, the greater workload will become.

esCalc

In esCalc, the desktop BI software, the increments in the column E are calculated in the instant calculation cells. You can calculate before or after grouping. Because the increment of the first trading day has been set as 0, you will have to edit the cell value manually after the instant calculation cells finish the calculations.

Sort the weekly stock data by daily increment:

In the instant calculation cells of esCalc, formulas calculate only once after they are entered. The subsequent operations will not affect the existing result. In esCalc, the sort operation can be carried out on all homorows in their respective groups at the same time. The operation will not be affected by the data volume or the number of levels of groups. It is convenient and prompt.