2013/7/10 Johnny Rosenberg <[email protected]>: > 2013/7/10 Johnny Rosenberg <[email protected]>: >> Once again I sent privately. I'm getting tired of this so I really >> don't care, but I decided to be nice today, so here's to the list. >> >> I also added some stuff at the end. >> >> >> ---------- Forwarded message ---------- >> From: Johnny Rosenberg <[email protected]> >> Date: 2013/7/10 >> Subject: Re: [libreoffice-users] Auto-sort group of cells when any >> cell is modified/saved? >> To: Tanstaafl <[email protected]> >> >> >> 2013/7/9 Tanstaafl <[email protected]>: >>> Is it possible to define a range of cells to auto sort themselves, such that >>> anytime one of them is modified, the sort is reapplied? >>> >>> This is some Sales Numbers for some Sales Reps, and the boss wants them to >>> always be sorted based on the total column anytime any numbers in the sheet >>> are changed. Currently I'm manually sorting the sheet every morning, but >>> he'd like this to happen automatically. >>> >>> Thanks >> >> I'm not sure you can do it exactly like that. You can make a macro to >> sort your specific cells, either when you run it manually or at a >> specific event. Those events are ”when the document is opened” and >> things like that, I don't think you can trig it to ”when a cell in >> A1:F19 is modified” or anything like that. >> >> I would just add a button in the sheet that sorts when clicked. As a >> complement to that, I would probably also trig it to when document is >> opened and maybe saved, if that's possible. >> >> >> Another way is to use cell formulas to keep your cell range sorted. >> It's possible but somewhat complicated. I have done it, but I need to >> do some searching, because I don't remember in which document I did >> this… >> In that case you will probably want to use two sheets: One for data >> input and one for viewing (which is the auto-sorted one). >> >> If you want to sort by a column with numbers, take a look here: >> http://ask.libreoffice.org/en/question/1309/is-there-an-automatic-sort-function-in-calc/ >> >> I'm doing some experiments right now to find out if this also can be >> achieved with text… Maybe I'll fail, I don't know… yet… >> >> >> >> Johnny Rosenberg >> >> >> I did some testing and I finally found how to auto-sort text, rather >> than just numbers. It's probably slow with big cell ranges though, >> since I needed to write a cell function to get the job done. >> >> I'm sure there are much better ways to do this, but this is what I came up >> with: >> >> First create a new cell function called SortValue. The function >> returns a number between 0 and 1 corresponding to the input text. >> This function is VERY simple and there is no error handling at all, so >> feel free to improve it and customise it after your likings! >> >> REM ***** BASIC ***** >> >> Option Explicit >> >> >> >> Function SortValue(sText As String) >> Dim sSorted As String >> sSorted="0123456789abcdefghijklmnopqrstuvwxyzåäö" >> >> Dim iLen As Integer >> iLen=Len(sSorted) >> >> Dim d As Double, i As Integer >> sText=LCase(sText) >> For i=1 To iLen >> d=d+InStr(sSorted,Mid(sText,i,1))/iLen^i >> Next i >> >> SortValue=d >> End Function >> >> First of all, the string variable sSorted is defined as >> ”0123456789abcdefghijklmnopqrstuvwxyzåäö”. This is just the sort order >> for the characters it can handle. Feel free to remove and add >> characters to suite your needs. In this case, these are numbers >> followed by the Swedish alphabet. Only lower case characters are >> present since the macro converts the input string to lower case >> anyway. This way, ”A” gives the same numerical value as ”a”. If this >> isn't what you want, add uppercase letters to the list and place them >> right and remove the ”sText=LCase(sText)” line, or ”comment it out” by >> preceding the line with a ' or the text ”REM” (without the quotes). >> For example: >> sString="0123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz". >> Maybe you want to add other characters, like ”,.;:-” or whatever. >> I didn't test this with UNICODE characters, it could work, I think. >> >> By the way, my test document can be found here: >> http://ubuntuone.com/1I7EY6jMoZ4MYLrPejP5Eh >> Make sure that macro security is set so that you can run the cell >> formula properly, otherwise this will not work, of course. >> >> >> Now, if your text to be sorted is located at B1:B13, then add the >> following in A1: >> =SORTVALUE(B1) >> >> Copy downwards, so A2 contains =SORTVALUE(B2) and so on. >> >> In C1 we type: >> =SMALL(A$1:A$13;ROW()) >> >> Copy down the same way as we did with the A column… >> >> In D1: >> =VLOOKUP(C1;A$1:B$13;2;0) >> >> Copy down… >> >> And there we are! >> >> Of course this can be done with different spreadsheets, but the A and >> B column needs to be together in that order. However, you can hide the >> A column and other columns that you don't want to see, of course. >> >> >> Please note that I did this rather quickly. Of course I could have >> made a couple of mistakes here and there. I'm interested to know if >> you find any. Thanks. The only test I did was that document, see link >> above. >> >> >> >> Johnny Rosenberg > > I found one serious one myself, when entering the last character in a > cell (”ö” in this case). The result was > 1 which is supposed to be > impossible. > The mistake was of the embarrassing kind. I used the wrong length > value in the for loop (length of the sort order text instead of the > lenght of the input text)… > > Here's my corrected cell function: > > Option Explicit > > Function SortValue(sText As String) > Dim sSorted As String > sSorted="0123456789abcdefghijklmnopqrstuvwxyzåäö" > > Dim iLenSorted As Integer, iLenText As Integer > iLenSorted=Len(sSorted)+1 > iLenText=Len(sText) > > Dim d As Double, i As Integer > Dim iFoundPos As Integer > > sText=LCase(sText) > For i=1 To iLenText > iFoundPos=InStr(sSorted,Mid(sText,i,1)) > If iFoundPos>0 Then > d=d+iFoundPos/iLenSorted^i > End If > Next i > > SortValue=d > End Function > > Also note that doing this text to number conversion is probably not a > good idea if the text contains too many characters and the first ones > are the same, like: > abcdefghijkl ⇨ 0,282708744247206000 > abcdefghijlk ⇨ 0,282708744247206000 > > So in many cases, this method should not be used. See my example file > (which I modified since last time) at: > http://ubuntuone.com/1I7EY6jMoZ4MYLrPejP5Eh > > The next suggestion would probably be to create a cell function that > replaces the SMALL() function and works with text. > Maybe I will try that later, I don't know. Or perhaps someone else > will beat me to it. > > > > Johnny Rosenberg
I did some experiments with a Basic cell function that sorts a cell range and it works perfectly – when you run it from the Basic IDE, but it doesn't work as a Cell function, for some reason. It does everything right, but it just doesn't do the actual sorting… I'm not sure it's worth putting more efforts to this for now. After some searching I found this thread about an array sorting add-in written in Python. You may want to try it (I didn't though): http://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=56916 They were discussing it for a while so I guess you should try the add-in from the last post in that thread, but reading it all couldn't hurt, I guess. Also they are discussing Apache OpenOffice, but it might just work for LibreOffice as well. Johnny Rosenberg -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
