English Calc, version 4.2.6.3
I have a 4x6 array of cells, each of which has a formula. I want to grab the list of unique values out of this range, and put it in a new column, sorted. I have tried a few different solutions online, but I only get errors. How can I do this?
Could you please explain in more details which cells in the 4x6 array you select and how?
Things of that kind aren’t easy. Spreadsheets aren’t actually made for them.
SELECT DISTINCT Field FROM Table WHERE Condition SORT … is a typical SQL statement. Similar constructs are not available when using a spreadsheet.
To additionally complicate the situation you have organised a range containing data of same nature (otherwise listing and sorting them would be senseless) in a 2D-array. (This will not occur using a database.)
We can divide the task in 3 parts:
Reorganise the 2D-array into one column
Sort the entries of that column
Only collect the distinct items now (not changing the sort)
This to do by an apparatus of formulae requres some effort.
You may study the attached example if interested. To actually use such an apparatus of formulae additional measures may be required.Dev2D_1D_SortSelectDistinct001.ods
Lupp method works but does not scale. As of release 5.x LO does not have sort unique via Data->Sort menu. Suppose there are 100 or 1000 rows of data. Manual eyeball iden of unique then pick is error prone. Use a Pivot table as a workaround as it effectively does a SELECT GROUP BY which gives a list of unique & sorted values.