Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.