Grouping and Aggregation after Filtering

Below are sales amounts of some types of liquors. Find out the two places of production where the liquors have the top two highest sales for the two most popular types of liquors.

Excel

To summarize data according to the types of liquor, firstly you need to perform a sort:

Then aggregate each type of liquor to get the total sales amount. The result is shown below:

Sort the liquor types by the total sales amount. To do so, collapse rows to the level of type, and sort liquors by Amount in descending order. The result is shown below:

Expand the result, and only copy the sales data of the top two types of liquors in terms of sales amount to a new worksheet for further analysis. The result is shown below:

Since statistics are to be presented by places of production of the liquors, sort data ascendingly by Production:

Group data by Production and calculate the total sales amount for each place of production:

Collapse rows to the level of place of production, and sort data by the sales amount in descending order. The result is shown below:

You can view the final result from the Excel grid. If you need the details of the liquors from the top 2 places of production that have the highest sales amounts, then you will have to sort data out manually.

When using Excel to handle the data grouping and aggregation after filtering, you can complete the procedure easily though the filtering and sorting operations are a bit of complicated. However, since you need to summarize and analyze the filtered data, you will have to copy the filtered data or remove the undesired data manually. If the data volume or the number of groups is relatively great, the job would be hard, cumbersome, and error-prone.

esCalc

Group rows by the type of liquor. Select a cell to perform Sort before group, and then calculate the total sales amount for each group in the grouping row:

In esCalc, you can directly sort the groups. To do so, select D2 and perform sort in a descending order:

You can directly filter the groups:

In esCalc, you can carry out a regroup operation. To do so, select C4 to perform a group operation by checking Sort before group and Regroup options. The setting with the Group panel is shown below:

As you can see from the above result, the grouping row and the bands under it have been regenerated after regrouping. And the formulas in the grouping rows have recalculated.
Sort the grouping rows again:

The desired result is the groups ranking the top 2. To get them, you can filter the groups again.

Because esCalc, the user-friendly spreadsheet software, enables performing sort, filter, regroup, and other operations directly on the groups, you needn’t manually handle grouping and aggregation after filtering. Data processing can thus be carried out step by step, and each step is purposeful and simple, with result shown intuitively.