Locating desired data and filtering data to delete or hide the unwanted data are common data manipulation operations on a worksheet. esCalc offers locate operation to find the homo-cells satisfying your criteria in a sheet and filter operation to filter away the homo-bands that don’t satisfy them. Here we’ll focus our attention on the two operations.
1. Locate operation
You can click Operation>Locate on the right-click context menu to perform a locate operation, find the homo-cells you want and then move the cursor over there. You can also choose Operation> Locate from the menu bar, or use the shortcut key Ctrl+Alt+L.
The locate operation evaluates data in all homo-cells of the selected cell and moves the cursor upon the first homo-cell satisfying the specified condition. Let’s take the following population table as an example:
Select A4 and use the shortcut Ctrl+Alt+L to perform a locate operation. Then you’ll get the following pop-up locate window:
On the upper part of the window, you can set locating condition, and on the lower part there are a number of options for the operation – select all eligible homo-cells with Select all and search data cyclically with Wrap mode, and choose a locating direction from Forward and Backward.
To set a locating condition under Cell value, you can specify that the cell value is equal to, not equal to, greater than, not greater than, less than or not less than a certain value; or find the greatest or least value with Maximum or Minimum. Choose the value you want to specify from the drop-down list or type it in the space:
Below is the result of this locate operation:
Since the locating direction is Forward, the cursor moves to the cell where Aurora sits in the 6th row.
Suppose you selected the Cell value on the locate operation window and specified the condition as Maximum:
The execution would bring the following result:
The cursor is now on the city of San Jose that comes last in the alphabetical order.
You can check Expression to specify a locating condition as well. This way an expression for locating data can be entered. The expression uses @ to represents a cell value. For instance, select B3 in the above cellset, check Expression and set the filtering expression as @>800,000, which means finding cell values that are greater than 800,000, and then change the locating direction to Backward, meaning searching data up:
After the execution, you get this result:
The cursor has moved to B11. The locate operation searched homo-cells above B3 – not including B3 itself – but didn’t find the eligible ones, then it turned back to go on with searching up from the last homo-cell as the locating type was specified as Wrap mode, and finally positioned the cursor over the first-found homo-cell satisfying the condition. If you didn’t choose Wrap code, you would have got an error message since no eligible homo-cells was found above B3.
Now suppose you selected B10 to perform locate operation with the filtering expression @>800,000, the only locating direction Backward and locating type Wrap mode. In addition, you checked Select all:
Then you would get the following result:
With Select all and Backward being checked, the locate operation selected all eligible homo-cells above B10, which itself was included, and the cursor moved to the first-found homo-cell meeting the condition. Though B11’s value satisfied the condition, it wasn’t included because the locating direction Forward hadn’t been checked. By the way, you should keep in mind that the location is done among the homo-cells of a selected cell. This explains why B2, B5 and B8 in the same column, which are not B11’s homo-cells, have been ruled out, even though their values satisfy the condition.
2. Filter operation
You can choose Operation>Filter from the right-click context menu to perform a filter operation, which evaluates all the homo-cells of a selected cell so as to delete or hide the bands where the ineligible homo-cells reside. Another approach is to choose Operation> Filter on the menu bar, or press the shortcut key Ctrl+Alt+F.
Suppose you want to filter data in the above table and hide cities whose population is below 200,000. To do this, select B4 and perform the filter in the pop-up filter window:
You can set filtering condition in this window, which is similar to setting the locating condition. But an Enumeration option for specifying filtering condition is provided here, allowing you to choose multiple cell values you want to retain at a time. You can get rid of those bands that don’t meet the condition with Delete or Hide. And by checking Cell value, you can choose a sign and a value from the drop-down lists. But you cannot perform data filtering through specifying maximum and minimum values, which is different from locating operation.
Let’s check Cell value and set filtering condition as >200,000 and click Hide to hide the data that cannot satisfy this condition. Here is the filtering result:
You can see that the Aurora city in the 6th row is hidden because its population below 200,000.
You can also specify the filtering condition through an expression. For instance, select B4 to perform data filtering:
With Expression being checked, you can set the filtering condition as the expression @<{B3}.avg(), which means retaining data of cities whose population is below the average value, and then click Delete to remove the data that cannot meet this condition. Here is the result:
As can be seen, all the data of cities whose population is above the average value has been deleted. One thing worth noting is that the rows that were hidden also participated in the filtering. An examination of the row numbers will tell you that the data of Aurora city is still hidden and hasn’t been deleted, this is because it satisfies the condition.
Now if you select B3 and click Show in the filter window, the hidden homo-cell will show up again, as shown below:
The operation of reshowing homo-cells is irrelevant with the filtering condition.
The filter operation can also be performed on a parent row. Select A2, for instance, and perform filtering:
Check Enumeration and click the button on the right side to edit the data you want to retain in the pop-up window:
Click Delete to complete the operation, and then you’ll see the following result:
As can be seen, the data of California and Florida has been kept and other data has been removed through filter operation on A2 and its homo-cells. During the process, the whole band where the deleted rows reside will be removed, as well as their descendant rows and the slave rows in the same band.