An Excel sheet hasn’t sequence numbers to mark the relative positions of the data, so it is very inconvenient to filter detail data of each group automatically. Usually one can only handle the data manually and this requires a lot of work. With esCalc, however, one can handle them easily. Here is an example:
Compute in esCalc spreadsheet each salesperson’s three biggest orders, which are enclosed in the black box in the following figure:
And the following data:
The method of handling the problem in esCalc is simple. One just need to select the “FILTER” menu on column C and enter #<=3 into the dialog box. # represents the in-group sequence numbers, as shown below:
Only one step to complete the operation. That’s it.
But it’s not so easy to realize the operation in Excel. If one uses “top3” to filter all the data, the result will be as follows:
The resulting data are all zeroes, which makes no sense.
If each piece of data has its sequence number in its group, like what it is in esCalc, the problem will be made simpler. So one can create the in-group row numbers, set the sequence number of the summary row as zero and select all the rows whose sequence numbers are less than three.
First compute the in-group row number. The formula and the result are shown as follows:
Then filter the result and get the desired data. Obviously this method is a little complicated and far more inconvenient than esCalc’s, because it requires annoying formula and produces junk data.