sorting in calc

classic Classic list List threaded Threaded
7 messages Options
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

sorting in calc

Hello,
Is there a character i can enter into a cell that will come *before* numbers in the sort order?
I mean, before other cells that actually contain numbers rather than numerical strings

Thanks
Gary

--
To unsubscribe e-mail to: [hidden email]
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
Pedro Pedro
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

Gary Collins wrote
Hello,
Is there a character i can enter into a cell that will come *before* numbers in the sort order?
I mean, before other cells that actually contain numbers rather than numerical strings
Any character that shows up before zero in Character Map (if you are using Windows OS).
The very first character is ! but there are 14 others (e.g. #)

Hope this helps
Pedro
Dries Feys-2 Dries Feys-2
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

In reply to this post by Gary Collins
Gary,

You could check http://www.asciitable.com/ to see the order.

Met vriendelijke groeten, Salutations distinguées, Kind Regards,

DRIES FEYS
CORPORATE SERVICES • Specialist Software Developer

TVH GROUP NV
Brabantstraat 15 • BE-8790 WAREGEM
T +32 56 43 42 11 • F +32 56 43 44 88 • www.tvh.com
View our company movies via downloads on our website.


On 8 March 2017 at 19:59, Gary Collins <[hidden email]> wrote:

> Hello,
> Is there a character i can enter into a cell that will come *before* numbers in the sort order?
> I mean, before other cells that actually contain numbers rather than numerical strings
>
> Thanks
> Gary
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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

--


**** DISCLAIMER ****

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."

--
To unsubscribe e-mail to: [hidden email]
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
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

In reply to this post by Gary Collins
Hello Dries
Thank you for your reply. Yes i am aware of the ascii code but it doesnt seem to apply when some cells contain numbers rather than text. For example # comes before "1"  in the table but it sorts after numbers. I want something i can put in a cell which will sort before any number. I suspect there isnt anything. Maybe it might work if i can convert all the relevant  numbers i have entered to text but im still looking for a way to do that. Format>cells>numbers and selecting text doesnt do it, at least not in 5.2.2.2 under windows7. The format *appears* to change in that the numbers become left justified like strings but istext (cell) still returns false....
/G.
--------------------------------------------
On Thu, 9/3/17, Dries Feys <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] sorting in calc
 To: "Gary Collins" <[hidden email]>
 Cc: "[hidden email]" <[hidden email]>
 Date: Thursday, 9 March, 2017, 8:32
 
 Gary,
 
 You could check http://www.asciitable.com/ to see the
 order.
 
 Met vriendelijke
 groeten, Salutations distinguées, Kind Regards,
 
 DRIES FEYS
 CORPORATE SERVICES • Specialist Software
 Developer
 
 TVH GROUP NV
 Brabantstraat 15 • BE-8790 WAREGEM
 T +32 56 43 42 11 • F +32 56 43 44 88 •
 www.tvh.com
 View our company movies via
 downloads on our website.
 
 
 On 8 March 2017 at 19:59, Gary Collins <[hidden email]>
 wrote:
 > Hello,
 > Is
 there a character i can enter into a cell that will come
 *before* numbers in the sort order?
 > I
 mean, before other cells that actually contain numbers
 rather than numerical strings
 >
 > Thanks
 > Gary
 >
 > --
 > To unsubscribe e-mail to: [hidden email]
 > 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
 
 --
 
 
 **** DISCLAIMER ****
 
 http://www.tvh.com/glob/en/email-disclaimer
 
 "This message is
 delivered to all addressees subject to the conditions
 set forth in the attached disclaimer, which is
 an integral part of this
 message."
 
 --
 To
 unsubscribe e-mail to: [hidden email]
 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: [hidden email]
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
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

In reply to this post by Gary Collins
Hello all,
Ive managed to solve my problem in a roundabout way. To give the context, i have a column containing strings of length 1 to 3 which may contain nonstandard characters and which i must sort in a specific order (not the usual alphabetic order). To achieve this i have a table assigning a number (col B) to each char (col A) in sheet 2, defined as a range 'xlit'. On sheet 1 column A has strings to be sorted, columns B-D contain number to represent char 1,2,3 of the string. I obtain these numbers as text, using a formula of the form =IFERROR(TEXT(VLOOKUP(MID (Ax,y,1),xlit,2,0),"General"")," ")
where x is the row containing the string and y is 1,2 or 3 for the appropriate char of the string, returning the number as a string or " "if the character doesnt exist (string is of shorter length). I can now order my strings by sorting on columns B,C,D in that order. Perhaps there might have been an easier way to do it, but it works and thats what counts
Gary
----------------------------------------
On Thu, 9/3/17, Gary Collins <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] sorting in calc
 To: "Gary Collins" <[hidden email]>, "Dries Feys" <[hidden email]>
 Cc: "[hidden email]" <[hidden email]>
 Date: Thursday, 9 March, 2017, 9:12
 
 Hello Dries
 Thank you for your reply. Yes i am aware of the ascii code
 but it doesnt seem to apply when some cells contain numbers
 rather than text. For example # comes before "1"  in
 the table but it sorts after numbers. I want something i can
 put in a cell which will sort before any number. I suspect
 there isnt anything. Maybe it might work if i can convert
 all the relevant  numbers i have entered to text but im
 still looking for a way to do that.
 Format>cells>numbers and selecting text doesnt do it,
 at least not in 5.2.2.2 under windows7. The format *appears*
 to change in that the numbers become left justified like
 strings but istext (cell) still returns false....
 /G.
 --------------------------------------------
 On Thu, 9/3/17, Dries Feys <[hidden email]>
 wrote:
 
  Subject: Re: [libreoffice-users] sorting in calc
  To: "Gary Collins" <[hidden email]>
  Cc: "[hidden email]"
 <[hidden email]>
  Date: Thursday, 9 March, 2017, 8:32
 
  Gary,
 
  You could check http://www.asciitable.com/ to see the
  order.
 
  Met vriendelijke
  groeten, Salutations distinguées, Kind Regards,
 
  DRIES FEYS
  CORPORATE SERVICES • Specialist Software
  Developer
 
  TVH GROUP NV
  Brabantstraat 15 • BE-8790 WAREGEM
  T +32 56 43 42 11 • F +32 56 43 44 88 •
  www.tvh.com
  View our company movies via
  downloads on our website.
 
 
  On 8 March 2017 at 19:59, Gary Collins <[hidden email]>
  wrote:
  > Hello,
  > Is
  there a character i can enter into a cell that will come
  *before* numbers in the sort order?
  > I
  mean, before other cells that actually contain numbers
  rather than numerical strings
  >
  > Thanks
  > Gary
  >
  > --
  > To unsubscribe e-mail to: [hidden email]
  > 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
 
  --
 
 
  **** DISCLAIMER ****
 
  http://www.tvh.com/glob/en/email-disclaimer
 
  "This message is
  delivered to all addressees subject to the conditions
  set forth in the attached disclaimer, which is
  an integral part of this
  message."
 
  --
  To
  unsubscribe e-mail to: [hidden email]
  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: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

In reply to this post by Pedro
At 18:59 08/03/2017 +0000, Gary Collins wrote:
>Is there a character i can enter into a cell
>that will come *before* numbers in the sort
>order? I mean, before other cells that actually
>contain numbers rather than numerical strings

What about some value less than any of your real
data are (or can be)? What is the range of your
numerical values? In practice, this is unlikely
to span the entire range that Calc can handle. If
your values are all positive and non-zero, for
example, then zero will do. Otherwise a sentinel
value such as -999 might suffice.

If you don't want this number to appear in your
list, you can hide it using Conditional
Formatting with a cell style having its font
colour set to white (or whatever is your cell
background colour). Alternatively, you can set
the cell format of your range itself to something like [WHITE][<=0]0;0 .

But the number would still be there, of course,
so you'd have to take care if you did any
calculations on the range such as AVERAGE().

At 09:12 09/03/2017 +0000, Gary Collins wrote:
>Maybe it might work if i can convert all the
>relevant  numbers i have entered to text but im
>still looking for a way to do that.
>Format>cells>numbers and selecting text doesn't do it, ...

No: changing format of cells already containing
values never changes the stored values
themselves. But there are two alternative easy ways to do this:

o In another column, enter =TEXT(Xn;"0") (or
whatever is a suitable format string).
o Fill down the column.
o Select the new values and cut.
o Paste them over the originals, but using Edit |
Paste Special... (or Ctrl+Shift+V), selecting
Numbers but not Formulae in the Paste Special dialogue.

o Select the range of values.
o Go to Data | Text to Columns... .
o In the Text to Columns dialogue, under Fields,
click on the column header (probably currently Standard).
o In the "Column type" drop-down, select Text.
o OK.
Voilà!

At 10:15 09/03/2017 +0000, Gary Collins wrote:
>Ive managed to solve my problem in a roundabout
>way. To give the context, i have a column
>containing strings of length 1 to 3 which may
>contain nonstandard characters and which i must
>sort in a specific order (not the usual
>alphabetic order). To achieve this i have a
>table assigning a number (col B) to each char
>(col A) in sheet 2, defined as a range 'xlit'.

So this table must indeed have a small, finite
range of values (presumably integers) in column
B. Surely your lookup formula just needs to
ascribe some numeric value outside and below this
range instead of the blank character or empty
cell that you may have at present?

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: sorting in calc

In reply to this post by Gary Collins
Thanks, yes, theres some really helpful info in this reply. I had thought of using a lower value but it hadnt occurred to me to hide it using formatting (and of course i didnt want it to show). The methods of converting to text are very useful to know. In the end i did indeed use the TEXT function in a formula, and conditionally substituted a blank where the string was shorter. Again not exactly elegant but it works and thats the main thing. It would be helpful to be able to define a nonstandard sort order but i dont think thats easily possible in a case like this (?: but im very willing to be proved wrong on that 😊) Again i think i'd be into the realm of macros!

Thanks
G.
--------------------------------------------
On Thu, 9/3/17, Brian Barker <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] Re: sorting in calc
 To: [hidden email]
 Cc: "Gary Collins" <[hidden email]>
 Date: Thursday, 9 March, 2017, 23:17
 
 At 18:59 08/03/2017
 +0000, Gary Collins wrote:
 >Is there a
 character i can enter into a cell
 >that
 will come *before* numbers in the sort
 >order? I mean, before other cells that
 actually
 >contain numbers rather than
 numerical strings
 
 What
 about some value less than any of your real
 data are (or can be)? What is the range of your
 
 numerical values? In practice, this is
 unlikely
 to span the entire range that Calc
 can handle. If
 your values are all positive
 and non-zero, for
 example, then zero will
 do. Otherwise a sentinel
 value such as -999
 might suffice.
 
 If you
 don't want this number to appear in your
 list, you can hide it using Conditional
 Formatting with a cell style having its font
 
 colour set to white (or whatever is your
 cell
 background colour). Alternatively, you
 can set
 the cell format of your range
 itself to something like [WHITE][<=0]0;0 .
 
 But the number would still be
 there, of course,
 so you'd have to take
 care if you did any
 calculations on the
 range such as AVERAGE().
 
 At
 09:12 09/03/2017 +0000, Gary Collins wrote:
 >Maybe it might work if i can convert all
 the
 >relevant  numbers i have entered
 to text but im
 >still looking for a way
 to do that.
 >Format>cells>numbers
 and selecting text doesn't do it, ...
 
 No: changing format of cells
 already containing
 values never changes the
 stored values
 themselves. But there are two
 alternative easy ways to do this:
 
 o In another column, enter
 =TEXT(Xn;"0") (or
 whatever is a
 suitable format string).
 o Fill down the
 column.
 o Select the new values and cut.
 o Paste them over the originals, but using Edit
 |
 Paste Special... (or Ctrl+Shift+V),
 selecting
 Numbers but not Formulae in the
 Paste Special dialogue.
 
 o
 Select the range of values.
 o Go to Data |
 Text to Columns... .
 o In the Text to
 Columns dialogue, under Fields,
 click on
 the column header (probably currently Standard).
 o In the "Column type" drop-down,
 select Text.
 o OK.
 Voilà!
 
 At
 10:15 09/03/2017 +0000, Gary Collins wrote:
 >Ive managed to solve my problem in a
 roundabout
 >way. To give the context, i
 have a column
 >containing strings of
 length 1 to 3 which may
 >contain
 nonstandard characters and which i must
 >sort in a specific order (not the usual
 >alphabetic order). To achieve this i have a
 
 >table assigning a number (col B) to
 each char
 >(col A) in sheet 2, defined
 as a range 'xlit'.
 
 So this table must indeed have a small, finite
 
 range of values (presumably integers) in
 column
 B. Surely your lookup formula just
 needs to
 ascribe some numeric value outside
 and below this
 range instead of the blank
 character or empty
 cell that you may have
 at present?
 
 I trust this
 helps.
 
 Brian Barker
 
 
 --
 To
 unsubscribe e-mail to: [hidden email]
 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: [hidden email]
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
Loading...