Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?

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