Is there an automatic sort function in Calc?

[TJMeneses]

I’ve been wondering about this for quite some time, because some of my uses of Calc requires me to input data and then sort it right after. I’ve been looking around the internet and various other boards for a good answer, but the lack of a straightforward solution implies that the ultimate answer is “no”.

Still, if anyone knows of a specific function I could apply to a list of data that will keep it autosorted after I’ve typed my data, I’d really like to know. Two possible use cases of an automatic sort function would be: a) the list I’m typing into is the one being automatically sorted, and b) the data in the list is copied into another list, perhaps into another sheet, and autosorted there.

[artfulrobot]

(I am not allowed to post answers yet) Why not use a Pivot Table. It won’t automatically update, but it’s only a right-click menu then click Refresh to update it.

[jofilho]

First… you need to think about your whole sheet. Like… where do you need your ordered data? Could you have an area for input and other area for display the ordered data? So, you need to know your necessities and think about a way to work around the problems, using the space that you have in your sheet. A simple function, as I said, doesn’t exist. I don’t know your especific problem, so I can’t help with a especific solution. But I think I can sugest… Look for functions like SMALL(), LARGE(), VLOOKUP()… you can do a lot of things with these kink or functions, since you are using your whole sheet.

[TJMeneses]

Thanks for the function references. I’ll look them up when I can.

[MegaTallDave]

I can think of two ways to do this.

If your list of numbers was from A1 to A10, then you could first have a list of 1 through 10 in the B column right next to it.

Then use =small(a$1:a$10,b1) in cell c1 and then extend this function. This way c1 has the smallest, c2 the next smallest and so on.

Second, if you want to get fancy you could try

=small(a$1:a$10,row())

But if you want to start on a row other than 1, you have to make sure you subtract to take that into account.

So if your data was in cells a3 through a14 you would use
=small(a$3:a$14,row()-2)

[TJMeneses]

Thanks for the short but practical example! I consider this to be the best non-answer (workaround); apart from simply providing the functions that could be used, you actually used them to show how they autosort lists.

[jofilho]

Sorry, but the answer is… no! A specific and automatic function doesn’t exist. But we have others very good solutions. Maybe you can change your mind and think about an other option instead of a simple function…

[TJMeneses]

Okay, I think it’s sinking in to me that there isn’t any autosort function in Calc. Could you discuss (or point me to) the other “very good solutions” you mentioned? Because I don’t have a clue as to how to even approximate the functionality I need. Anything but macros, though.

[ArielHuang]

yes you can sort Excel/Libreoffice using just formulas only.

My solution is much simpler.

1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function

2. My sorted numbers shall be in cells J29-O29.

3. The formula for cell J29 is
   =IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))

4. The formula for cell K29 is … just convert all the “=1″ into “=2″

5. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.

6. The RANK function will rank every cell in the range. There will not be any unranked.

7. The last part … ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
   If there are more than one same number … meaning there are more than one number of the same rank… it would duplicated the previous number of the same rank.

8. To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.

Hope this would help u guys.
Ariel

[dbird]

Did this. Works good for the limited number of items to sort, which was perfect for my situation.

Thanks,

Dave

[johncpratt1]

If you don’t want to use so many =IF statements, a way to make this simpler is to use VLOOKUP.

[ErrolG]

Yes, there is an auto-sort function, if you combine RANK, VLOOKUP and COUNTIF.

Assuming you have a table of numbers (#1) with totals in the end column,
to get a duplicate table (#2) auto-sorted when the #1 totals change,
(1)In a column to the left of #1, use RANK to sort the column.
(2)In a column to the left of #2, have 1 to last and use VLOOKUP to grab the appropriate number.

The problem is duplicate totals in #1, because RANK assigns different instances the same ranking, which confuses VLOOKUP. To solve this,
(3). In a column to the right of (1), use COUNTIF to identify the duplicates, with a shrinking array to exclude the current (1)
(4)In a column to the right of (3), add (1) and 1/(3).
(5)Repeat RANK for (4). This gives the final sort values, with no duplicates.
(6)Repeat (2), pointing to (5).

This auto-sorts Table 2 whenever Table 1’s end column changes.

[johncpratt1]

Alternatively, you can add the row() value divided by 1000 (or 10,000, etc) to the rank to give it a unique rank, in case there are duplicates.