esCalc Solves Spreadsheet Problems – Filter Detail Data by Sequence Numbers

Uncategorized 284 0

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:

esCalc_problem_filter_sequence_1

And the following data:

esCalc_problem_filter_sequence_2

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:

esCalc_problem_filter_sequence_3

Only one step to complete the operation. That’s it.

esCalc_problem_filter_sequence_4

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:

esCalc_problem_filter_sequence_5

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:

esCalc_problem_filter_sequence_6

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.

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website