Sorting by five criteria at once

Discuss the spreadsheet application

Sorting by five criteria at once

Postby sensa » Fri May 16, 2008 4:02 am

Anyone know how to sort spreadsheet data using more than the three sort dropdown options? I need to sort by four, sometimes five criteria. Thx.
sensa
 
Posts: 2
Joined: Fri May 09, 2008 10:04 pm

Re: Sorting by five criteria at once

Postby acknak » Fri May 16, 2008 5:00 am

Some ideas:
Export the data and use an external program to do the sort. GNU sort can handle five fields.

Construct a 'helper column' (or columns) holding the sort key, something like: =CONCATENATE(A1;B1;C1;F1;G1). Not very practical if the sort is at all complicated.

Use Base: register your sheet as a data source, set up a database query with the sort fields you need, run the query to get the sorted output, then save that back into the original document.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting by five criteria at once

Postby Villeroy » Fri May 16, 2008 8:57 am

A query in Base has also the advantage that you can change the column order as well, and you can sort independent from the columns that are actually displayed.
Get columns labeled "Field3","Field2","Field1" in that order from "Sheet1"
and order by columns labeled "Field9", "Field8", "Field7", "Field6" where "Field8" and "Field6" should be sorted descending:
Code: Select all   Expand viewCollapse view
SELECT "Field3","Field2","Field1"
FROM "Sheet1"
ORDER BY "Field9", "Field8" DESC, "Field7", "Field6" DESC

SELECT * FROM "Sheet1" selects all columns from Sheet1

If the sheet in question contains more used cells than the list to be sorted, select the range to be sorted and add a database range (Data>Define...). The defined range will appear as a separate table in Base.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28569
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Zizi64 and 28 guests