The easiest way to sort data in Calc is to use Data, Sort from the menu. However this does not keep the data sorted if changes are made to it after sorting. It is possible to construct formulas which will maintain a copy of the data in sorted order. The first sheet in the attached file shows how to do that. It provides for sorting up to 20 numbers, of which 16 are currently in use. The capacity of the sheet can be expanded beyond 20 by adjusting the text $21, which occurs in some of the formulas, to a larger number using the Edit, Find & Replace dialog and then filling the formulas down to unused rows with the fill handle. See OOo Help if you don't know about the fill handle.
There are some disadvantages to this technique, however:
- The formulas are complicated to build, and doing so may take more of your time than simply using Data, Sort even if you have to do that dozens of times.
- You must make a copy of the data because formulas cannot sort "in place", so the size of your spreadsheet will grow.
- For large amounts of data, the formulas will be slow because they are inefficient. Data, Sort performs just one sort. But for each row the formula sorts all 20 items, and then discards 19 of them, as explained below. Thus for 20 rows, 20 individual sorts are performed. On current hardware you won't notice a delay for 20 items, but you probably will for 20,000 items, which implies 20,000 sorts of 20,000 items.
Filtering data
The easiest way to filter data in Calc is to use Data, Filter from the menu. However this also needs to be reapplied if changes are made after filtering. It is possible to construct formulas which will maintain a copy of the data after performing filtering. The second sheet in the attached file shows how to do that. It provides for filtering up to 20 numbers, of which 16 are currently in use. The capacity of the sheet can be expanded beyond 20 by adjusting the text $21, which occurs in some of the formulas, to a larger number using the Edit, Find & Replace dialog and then filling the formulas down to unused rows. The disadvantages of the filtering formulas are similar to those for the sorting formulas, though inefficiency is less of a concern.
Here's how the formulas in the second sheet work. Cell B2 uses COUNTA to determine how many of the 20 cells in the first column are in use. Column C is the formula which is used to determine if the item should be included by the filter. My example tests to see if the item is an odd number, but it can be any test that returns a TRUE/FALSE value. Column D locates the positions of the successive TRUE values. We'll look at cell D5. D4=4 has previously determined that the third TRUE item is the 4th one (for 61). The OFFSET function in D5 selects the last twelve (16-4) items in C. These values (in C6 through C17) correspond to the data after 61, that is 908 through 703. In Calc, formulas which evaluate to TRUE have the value 1. The MATCH function looks for the first 1 value in the last twelve items. In this case it is the third one, cell C8. Thus MATCH(…) is 3 and when added to D4=4 the formula determines that the position of the next TRUE value is the seventh item (C8). The formulas in D12 through D21 evaluate to an error value because MATCH cannot find more TRUE values. In column E we test to see if an error occurred in column D and if so supply an empty string as the value. Otherwise INDEX selects the position determined by column D, so E5 picks the seventh (D5=7) item in column A (897).
Filtering and sorting data
Finally, one can combine these techniques to obtain a filtered, sorted list. The third sheet in the attached file shows how to do that. Note that due to the use of COUNTA in B2 of the first sheet, the sorting formulas there will not work properly if any of the cells in A2 thorough A17 are empty. If your list contains empty cells and you want formulas which will created a dynamically sorted list, use the technique on sheet 3 and NOT(ISEMPTY(…)) as your test in column C.
Since this is intended as a tutorial, the sheets in the attachment use several auxiliary columns to hold intermediate calculations. These make it easier to understand how the formulas work. Depending on your requirements and your skill level, you may want to combine the formulas so that fewer auxiliary columns are needed. The attachment does not use any Array formulas, but that technique can also be used to sort/filter with fewer (perhaps even zero) auxiliary columns at the expense of longer and more complicated formulas. Someone may want to post an advanced tutorial for an array function solution.
There are no doubt many alternate ways to write these formulas. You could use OFFSET where I've used INDEX, though different parameters would be needed. So if you're wondering why these methods were chosen, it was because I thought that they were simple to understand for a tutorial, and because I liked them. (De gustibus non disputandum est.) If you prefer, for example, another method for locating and selecting the TRUE values in column C of the second sheet, definitely go ahead and use it instead. But this one has been tested and works. The colors in the second and third sheets are produced by conditional formatting and are only meant to help you quickly identify the odd values. The sheets will work if all the conditional formatting is removed (with Format, Default formatting).
I would advise beginners to avoid all of these techniques, unless you fully understand how they work, and just use Data, Sort and/or Data, Filter.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.