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