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


Hi all

You can get to a auto-sorted table in 4 steps without macros by using vlookup, hlookup, count, max and min. Ive sent Charles a working example for 20 salespeople over 12 months, where the duplicate list re-sorts as soon as the YTD total changes.

I couldn't bear the thought of Charles spending valuable coffee time manually sorting tables.
Errol


On 2013/07/11 03:05 PM, Tanstaafl wrote:
Thanks for your efforts Johnny, sorry I didn't reply sooner.

We do actually add the data into a separate sheet already, then the sheet that we want to be sorted just references those cells for each Sales Rep.

But... this all looks a bit too complicated/fragile for me to be comfortable with it. This spreadsheet is important to the boss, and I'd hate to start having problems caused by adding something like this to the mix.

I'll look into the extension, but I'm leaning toward just telling him it may be best to just assign someone the job of sorting the thing once every morning and just live with it...

Thanks again for all of your responses and looking into it!

Charles

On 2013-07-10 5:29 PM, Johnny Rosenberg <gurus.knugum@gmail.com> wrote:
2013/7/10 Johnny Rosenberg <gurus.knugum@gmail.com>:
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

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: 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.