Ask Your Question

Is there an automatic sort function in Calc? [closed]

asked 2012-03-18 07:48:10 +0200

TJ Meneses gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-03 21:43:08.011637


(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.

artfulrobot gravatar imageartfulrobot ( 2014-06-25 12:55:29 +0200 )edit

5 Answers

Sort by » oldest newest most voted

answered 2012-03-18 09:04:43 +0200

jofilho gravatar image

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...

edit flag offensive delete link more


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.

TJ Meneses gravatar imageTJ Meneses ( 2012-03-18 09:59:33 +0200 )edit

answered 2012-03-19 08:24:49 +0200

jofilho gravatar image

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.

edit flag offensive delete link more


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

TJ Meneses gravatar imageTJ Meneses ( 2012-03-30 16:41:43 +0200 )edit

answered 2012-03-20 18:06:19 +0200

MegaTallDave gravatar image

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


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)

edit flag offensive delete link more


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.

TJ Meneses gravatar imageTJ Meneses ( 2012-03-30 16:43:40 +0200 )edit

answered 2013-07-11 17:59:30 +0200

ErrolG gravatar image

updated 2013-07-11 23:32:13 +0200

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.

edit flag offensive delete link more


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.

jpratt2 gravatar imagejpratt2 ( 2020-05-16 15:17:38 +0200 )edit

answered 2012-08-22 08:36:53 +0200

Ariel Huang gravatar image

updated 2012-08-22 08:40:00 +0200

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 :D

  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

edit flag offensive delete link more


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



dbird gravatar imagedbird ( 2017-03-22 07:30:40 +0200 )edit

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

jpratt2 gravatar imagejpratt2 ( 2020-05-16 15:41:24 +0200 )edit

Question Tools


Asked: 2012-03-18 07:48:10 +0200

Seen: 20,288 times

Last updated: Jul 11 '13