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.
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...
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 ( 2012-03-18 09:59:33 +0200 )editFirst... 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.
Thanks for the function references. I'll look them up when I can.
TJ Meneses ( 2012-03-30 16:41:43 +0200 )editI 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)
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 ( 2012-03-30 16:43:40 +0200 )edityes you can sort Excel/Libreoffice using just formulas only.
My solution is much simpler.
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
My sorted numbers shall be in cells J29-O29.
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))))))
The formula for cell K29 is … just convert all the “=1″ into “=2″
The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.
The RANK function will rank every cell in the range. There will not be any unranked.
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.
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
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-03-18 07:48:10 +0200
Seen: 2,138 times
Last updated: Aug 22 '12
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
