2013/7/11 Errol Goetsch <er...@xe4.org>: > 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.
Why not upload it somewhere and give us the link? Maybe someone else want to study it. Me, for instance… Johnny Rosenberg > > 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.knu...@gmail.com> wrote: >>> >>> 2013/7/10 Johnny Rosenberg <gurus.knu...@gmail.com>: >>>> >>>> 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 >>> >>> >>> 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+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 > -- 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
