2013/7/10 Johnny Rosenberg <gurus.knu...@gmail.com>: > 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 <gurus.knu...@gmail.com> > Date: 2013/7/10 > Subject: Re: [libreoffice-users] Auto-sort group of cells when any > cell is modified/saved? > To: Tanstaafl <tansta...@libertytrek.org> > > > 2013/7/9 Tanstaafl <tansta...@libertytrek.org>: >> 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 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 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