Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
https://docs.google.com/file/d/0B6LXy9sguZVkSXVqdnBWd0tVWUk/edit?usp=sharing 2 ways to auto-sort a numerical table using simple formulae when cells in a totals column are modified On 2013/07/11 06:00 PM, Dave Barton wrote: Johnny Rosenberg wrote: 2013/7/11 Errol Goetsch : 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â?S( Johnny Rosenberg +1 Dave -- -- 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
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
Johnny Rosenberg wrote: > 2013/7/11 Errol Goetsch : >> 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 +1 Dave -- 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
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
2013/7/11 Errol Goetsch : > 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 wrote: >>> >>> 2013/7/10 Johnny Rosenberg : >>>> >>>> 2013/7/10 Johnny Rosenberg : >>>>> >>>>> 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 >>>>> Date: 2013/7/10 >>>>> Subject: Re: [libreoffice-users] Auto-sort group of cells when any >>>>> cell is modified/saved? >>>>> To: Tanstaafl >>>>> >>>>> >>>>> 2013/7/9 Tanstaafl : >>>>>> >>>>>> 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... >>>>> >>>>> >>>>> >>>>>
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
Thanks Errol, Trying to work out how it works as we speak... :) On 2013-07-11 10:58 AM, Errol Goetsch wrote: 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 -- 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
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
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 wrote: 2013/7/10 Johnny Rosenberg : 2013/7/10 Johnny Rosenberg : 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 Date: 2013/7/10 Subject: Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved? To: Tanstaafl 2013/7/9 Tanstaafl : 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:
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
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 wrote: 2013/7/10 Johnny Rosenberg : 2013/7/10 Johnny Rosenberg : 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 Date: 2013/7/10 Subject: Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved? To: Tanstaafl 2013/7/9 Tanstaafl : 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
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
2013/7/10 Johnny Rosenberg : > 2013/7/10 Johnny Rosenberg : >> 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 >> Date: 2013/7/10 >> Subject: Re: [libreoffice-users] Auto-sort group of cells when any >> cell is modified/saved? >> To: Tanstaafl >> >> >> 2013/7/9 Tanstaafl : >>> 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 >&g
Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
2013/7/10 Johnny Rosenberg : > 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 > Date: 2013/7/10 > Subject: Re: [libreoffice-users] Auto-sort group of cells when any > cell is modified/saved? > To: Tanstaafl > > > 2013/7/9 Tanstaafl : >> 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: &g
Clunkiness of the list, was: Fw: Fwd: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
Hi :) I have started using "Reply to all" for my normal emails too and mostly that works just fine. Occasionally i have to be careful and edit out certain people but mostly it works better than trying to work out which button to press for different things. Regards from Tom :) - Forwarded Message - >From: Johnny Rosenberg >To: LibreOffice Användare >Sent: Wednesday, 10 July 2013, 13:41 >Subject: Fwd: [libreoffice-users] Auto-sort group of cells when any cell is >modified/saved? > > >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 >Date: 2013/7/10 >Subject: Re: [libreoffice-users] Auto-sort group of cells when any >cell is modified/saved? >To: Tanstaafl > > >2013/7/9 Tanstaafl : >> 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 th
Fwd: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
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 Date: 2013/7/10 Subject: Re: [libreoffice-users] Auto-sort group of cells when any cell is modified/saved? To: Tanstaafl 2013/7/9 Tanstaafl : > 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 -- To unsubscribe e-mail to: users
[libreoffice-users] Auto-sort group of cells when any cell is modified/saved?
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 -- 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
