With a spreadsheet, often we need to re-sort data we work with to make it more organized according to the computing target. An esCalc sort operation can sort data efficiently. At times the desired order is not a simple ascending or descending order. In those cases the align operation can be used to sort data in a specified order. Here let’s learn about the sort and align operations.
1. Sort operation
To perform a sort operation, select a cell and choose Operation>Sort on the right-click context menu. The operation will sort all the homo-rows in ascending or descending order based on the value of the selected homo-cell in the current row. And this sorting of homo-rows will be performed separately in the scope of each parent row. esCalc also adds Operation>Sort on the menu bar and provides a shortcut key Ctrl+Alt+S to do this.
We’ll illustrate the sort operation through the following employee table:
In this table, the employee records are arranged in an ascending order by their IDs. Now you need to sort them according to the departments. So let’s choose B3 and press Ctrl+Alt+S to perform the sort operation, and the following Sort window will pop up:
This Sort window shows the cell selected for performing the sorting and the option of ascending order. When there are multiple sorting conditions, you can change their order by clicking Up and Down buttons on the right side. By checking Use Locale, the sorting will be done alphabetically according to a designated language. Let’s check the ascending order Asc and perform the sort operation based on B3’s value. Here’s the result:
You can see that all the homo-rows, including the 3rd row, have been sorted alphabetically by the departments in ascending order, while the order of the records in the same department remain unchanged. The effect will be the same whichever homo-cell of B3 is chosen.
The sort operation permits you to select more than one cell at the same level in a band, as well as to set their priorities. You can also press Ctrl to select multiple discontinuous cells.
In the above table, select B11 and C11 together to perform a sort by B11 in descending order and then by C11 in ascending order. The following is the setting:
And the sorting result:
First all employee records are sorted alphabetically by departments in descending order and then records in each department are re-sorted alphabetically in ascending order.
Let’ take a look at another employee table, where data is grouped by departments. About the group operation, you can refer to esCalc Operations: Removing Duplicates and Grouping Data.
The data needs to be sorted by departments in descending order. To do this let’s choose B2 to perform the sorting in the specified order and here’s the result:
As can be seen from the result, when data was sorted alphabetically by departments in descending order, the descendant rows of each department moved together. But the move of the master row didn’t change the order of its sub-rows.
Then let’s sort data by ages in ascending order. This is equal to sorting birthdays in descending order. To perform this Sort operation, choose B3 and leave Asc blank. And here’s the result:
You can see that the operation has been carried out in B3’s homo-rows simultaneously yet it only has had effect on each band defined by the original parent row separately, without moving records between the bands.
2. Align operation
The sort operation can only sort data in ascending or descending order based on the cell value. So sometimes it just goes wrong, like what will happen with following table:
In column E, you’ve computed through formulas the months when each employee was born, like the formula =string(D2,”MMM”) in E2. In order to prepare birthday parties for employees, you want to sort the records by months. It is at this point when the sort operation fails. Because you cannot get a result the same as the actual order of the months either, i.e. Jan, Feb, Mar, Apr, …, by sorting them alphabetically in either ascending or descending order. In this case, you can use align operation to sort the data according to the specified order.
You can perform the align operation by selecting a cell and choose Operation>Align on the right-click context menu. The operation will sort all the homo-rows according to the position of the value of the selected cell of the current row in a specified sequence. Still the sorting will be performed separately in the scope of each parent row. You can also perform the operation by choosing Operation>Align from the menu bar or by pressing the shortcut key Ctrl+Alt+A.
For instance, choose E2 in the above table and press the shortcut key Ctrl+Alt+A to perform an Align operation. And an Align window will appear:
On this window, you can specify an Align sequence and name it, or search for an existing one by the name. Additionally there are three options: Align with sequence – whether or not sort data by the alignment sequence, Retain all band – whether or not retain all the bands, and Complete with sequence – whether or not fill in missing values with members of the sequence. When specifying a multi-layer alignment sequence, you can check Bottom level to use data of the smaller level.
First let’s do this according to the default setting, that is, select Align with sequence and Retain all band and name the alignment sequence AlignMonth, which is [Jan,Feb,Mar,Apr,May,Jun,Jul,Aug], as shown below:
When defining an alignment sequence, members are separated by carriage returns. Click OK to perform the align operation and here’s the result:
After an alignment sequence is specified, homo-rows, including the currently selected row, will be sorted according to this sequence. If these rows have slave rows or descendant rows, they will move along with the master rows or the parent rows. Since the alignment sequence consists of only the months from January to August, the align operation sorts the records in which the employees’ birthdays fall in these months, positioning employees whose birthdays are in the same month according to their original IDs. Because Retain all band is checked, records in which values of the homo-cells are not included in the alignment sequence are put at the end in their original order; otherwise they will be deleted. As Complete with sequence isn’t checked, the operation doesn’t supply values, like Jan. and Feb., which aren’t included in the original data.
Using align operation, you can deal with complicated multilayer data alignment, as the following table shows:
In a multilayer alignment, it is the data held by master cells that aligns with data at a higher level in the alignment sequence. So B2, B5 and B9 have been set as master cells. About the setting of master cells, you can read esCalc Structure: Homologous Relationship.
Here you want to perform a multilayer alignment according to the department data accommodated by the master cells at a higher level and the months in which the employees’ birthdays fall. Select C2 or any of its homo-cells to perform the Align operation. You can configure the information in the following Align window:
When defining a multilayer alignment sequence, values of two levels are separated by a tab and members are separated by carriage returns. Name the alignment sequence in this example, say, MultiLayer. The result of the align operation is as follows:
As can be seen from the result, the bands have been sorted in multilayer alignment according to the setting. During the multilayer align operation, the selected cell and its homo-cells correspond to the lower level of data in the alignment sequence and the cells at their parent levels correspond to the higher level of data. The cells at the parent levels participating in the align operation are master cells of those parent rows.
Without checking Retain all band, records in which values of both master cells at the parent levels and the homo-cells of the selected cell aren’t included by the alignment sequence have been deleted. Besides, the checking of Complete with sequence in the setting has resulted in the addition of some records, such as a record under Sales in which the value of the selected cell’s homo-cell is Mar, and the HR department to which a record in which the value of the selected cell’s homo-cell is Mar is added.
It is permitted that only a part of the data of a multilayer alignment sequence is used in an align operation, as shown through the following table:
Select B3 and choose MultiLayer, an existing alignment sequence, from the drop-down list of the align sequence name to perform the align operation. MultiLayer is a double-layer sequence, but you just want to use the department names at the higher level. Thus you can set Bottom level as 1 and check Align with sequence, as shown below:
Click OK and here’s the result:
The align operation often works with the group operation. For instance, sort records in a specified order and then group them, or group a band defined by a parent level and then sort records in a specified order.