[Solved] Sorting data numerically
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
[Solved] Sorting data numerically
I have a sheet that I am trying to sort by a reference number I have in column A. OOo Calc is insistent that 103,1000,etc come between 1 and 2. This is retarded. Am I missing something very basic here? Undoubtedly... Help?
Thank you in advance for your patience and assistance in enlightening the ignorant masses...
Thank you in advance for your patience and assistance in enlightening the ignorant masses...
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Sorting data numerically
Is your reference a number or text ?
Can you post an attachment here (make a dummy file) ?
Can you post an attachment here (make a dummy file) ?
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Sorting data numerically
ALmost certainly what is happening here is that your Column A is in text type and calc collates these alphabetically. so that 1,2,103,1000 get sorted into 1,1000,103 1000. A good trick to convert them to numeric is to select a cell with 0 in it, goto A1 the ndo a CTL-SHFT-downarrow to select to the last entry in A then do a Paste Special -> Add Values. This will add 0 to each entry and thereby convert then to numeric. The sort will then give you the answers that you are expecting
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
Re: Sorting data numerically
I originally created the file in Excel by importing a slightly modified assembly language file and kept everything formatted as text, as I wasn't planning on manipulating the data. Anyhow, I seems as though I ought to be able to select the column and format it as "number" and have the sort work numerically instead of "asciibetically". Perhaps I fantasize. I shall attempt to attach the sheet. I haven't tried TerryE's solution yet. I just got back from a job site and am only about 27% coherent. I will look at it more when I stand a chance of fitting it all in my head. I appreciate your input...thanks
- Attachments
-
- Variables.ods
- (22.96 KiB) Downloaded 501 times
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Re: Sorting data numerically
This sounds great but I couldn't get this trick to work Terry. Have I understood correctly?TerryE wrote:A good trick to convert them to numeric is to select a cell with 0 in it, goto A1 the ndo a CTL-SHFT-downarrow to select to the last entry in A then do a Paste Special -> Add Values. This will add 0 to each entry and thereby convert then to numeric.
Copy a zero to the clipboard
Select textual numbers and "Paste special Add" the zero over them
No matter what formatting the cells containing the textual numbers or the zero cell were, and no matter what other Paste special options were selected, the formatting of the destination cells just wouldn't change.
StarOffice 8u5
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Sorting data numerically
+1.huw wrote:This sounds great but I couldn't get this trick to work Terry. Have I understood correctly?
Even if I understand the trick and its logic, I can't make it work either in the file posted here or in a new spreadsheet.
Note that in the file here, I can't manage to make any calculation at all. I just have the formulas (and they're not to be displayed in the Tools>Options configuration.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Sorting data numerically
OK, I hereby DIE on my own sword.
This is an Excel trick, that I was absolutely sure that I'd moved into Calc and it worked, but I was wrong. This actually reveals quite an interesting functional difference between Excel and Calc and I think that what Excel does is far more rational (if I am allowed to utter such heresies here). Let's say that you are paste by value adding B1 to A1:
Anyway back to your problem. The easiest workaround is to insert a temporary column B where B1 =VALUE(A1) etc. then Paste Values B into A then delete the temporary B column.
Sorry to give you bad advice.
This is an Excel trick, that I was absolutely sure that I'd moved into Calc and it worked, but I was wrong. This actually reveals quite an interesting functional difference between Excel and Calc and I think that what Excel does is far more rational (if I am allowed to utter such heresies here). Let's say that you are paste by value adding B1 to A1:
- What Excel does is to assign =if(isnumber(value(A1)),value(A1)+B1,A1) to A1
- What Calc does is to assign =if(isnumber(A1),A1+B1,A1) to A1
Anyway back to your problem. The easiest workaround is to insert a temporary column B where B1 =VALUE(A1) etc. then Paste Values B into A then delete the temporary B column.
Sorry to give you bad advice.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
Re: Sorting data numerically
That trick seems to work. But what a pain in the posterior. What does "format cells" do if not equivalent to what I just did manually?
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Sorting data numerically
+1.badtoad1963 wrote:What does "format cells" do if not equivalent to what I just did manually?
Note that I also tried to Find and Replace the apostrophe (that tags the content as text) but it doesn't work
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Sorting data numerically
Urrgghh, You have to get into the Object model of OOo to answer this one. A cell has a value and a display format. These are quite separate. The value can be one of a range of types, such as integer, double, string, etc. If you enter a value into a cell then Calc does attempt to guess its type, so if you enter "12" in the input field then it will default to numeric (double), and "abc" will default to string. Likewise formulae return a type so in general arithmetic returns numbers and concatenation returns strings. Formats are purely do with how cell values are visualised on the display.
Numbers are coerced to strings when required so (A1+1)&" Bytes" produces the obvious result when A1 is a number, for example if A1=12 then this returns "13 Bytes".
In Excel the rule for coercing the other way is simple: if string values are used in numeric context and the string contains a valid number then it is coerced to the numeric value, otherwise to a bad value; so this expression returns
Numbers are coerced to strings when required so (A1+1)&" Bytes" produces the obvious result when A1 is a number, for example if A1=12 then this returns "13 Bytes".
In Excel the rule for coercing the other way is simple: if string values are used in numeric context and the string contains a valid number then it is coerced to the numeric value, otherwise to a bad value; so this expression returns
- 13 Bytes if A1='12
#VALUE! if A1=abc
- 1 Bytes if A1='12
1 Bytes if A1=abc
- 13 Bytes if A1=12
13 Bytes if A1='12
Err:502 if A1=abc
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: Sorting data numerically
Hi,
what's about using the Search & Replace facilities of Calc ?
Select the column you want to change from Text to numbers
Format-Cells-Numbers-General
Ok Button
CTRL+F
Search for '.*' without the quotes
Replace with &
Under more tag selection only and regular expressions
Click on Ok and the numbers should be numbers now.
Frank
what's about using the Search & Replace facilities of Calc ?
Select the column you want to change from Text to numbers
Format-Cells-Numbers-General
Ok Button
CTRL+F
Search for '.*' without the quotes
Replace with &
Under more tag selection only and regular expressions
Click on Ok and the numbers should be numbers now.
Frank
Re: Sorting data numerically
Presumably, you used the trick in Excel?badtoad1963 wrote:That trick seems to work. But what a pain in the posterior.
Edit: Answerd by PM
which was TerryE's helper column suggestion, directly above badtoad1963's post - I just wasn't reading the thread thoroughly.badtoad1963 wrote:Creating a temporary new column and setting it =VALUE the column I wanted to sort by worked in OOo. I don't even have Excel installed anymore.
You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.Hagar de l'Est wrote:Note that I also tried to Find and Replace the apostrophe (that tags the content as text) but it doesn't work :evil:
Last edited by huw on Thu Nov 29, 2007 11:00 am, edited 1 time in total.
Re: Sorting data numerically
This is the most elegant method so far, but it still highlights Calcs bizarre behaviour as to when it coerces to numeric.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Sorting data numerically
Thanks huw and Frank !huw wrote:You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.
I tag the thread as solved. badtoad1963 post again if you have any issue.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
Re: [Solved] Sorting data numerically
I'm not sure but I think I'm sorry I asked
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Re: [Solved] Sorting data numerically
I guess this is a painfully technical discussion for a simple question.
I know we've had this discussion time and again, but this is yet another strike against the way that Calc handles this (a numeric value stored as a string). It is simply wrong for badtoad to have to worry about Calc's inner object model just for doing a reasonable sort. If Calc is going to insist on not converting data behind the user's back (which is a worthy goal), then it should at least warn you when something "looks fishy" and give the user some idea what the issue is and how to fix it. I know I've seen Excel warn about it, but I don't even see anything under Tools > Detective for Calc.
I think the fact that, internally, Calc is inconsistent should be another clue that this is not the way to go. The search & replace is a handy trick, but it is surprising to me in the extreme. Is there any rationale for why a search & replace on a text value should cause it to change from a string to a number?
I'm used to Perl, which will coerce data in both directions naturally, along with providing standard idioms for guaranteeing string or numeric evaluation. It works very very well and I wish Calc would fix this huge problem.
There are going to be major changes in Calc's formula handling for OOo 3.0 (next fall) to comply with ODFF (Calc/ODFF Implementation [OOo wiki]). Unfortunately (last I checked) ODFF had punted on this issue and did not specify any 'correct' behavior. If this is ever going to be changed, however, I think this would be the time to do it.
I know we've had this discussion time and again, but this is yet another strike against the way that Calc handles this (a numeric value stored as a string). It is simply wrong for badtoad to have to worry about Calc's inner object model just for doing a reasonable sort. If Calc is going to insist on not converting data behind the user's back (which is a worthy goal), then it should at least warn you when something "looks fishy" and give the user some idea what the issue is and how to fix it. I know I've seen Excel warn about it, but I don't even see anything under Tools > Detective for Calc.
I think the fact that, internally, Calc is inconsistent should be another clue that this is not the way to go. The search & replace is a handy trick, but it is surprising to me in the extreme. Is there any rationale for why a search & replace on a text value should cause it to change from a string to a number?
I'm used to Perl, which will coerce data in both directions naturally, along with providing standard idioms for guaranteeing string or numeric evaluation. It works very very well and I wish Calc would fix this huge problem.
There are going to be major changes in Calc's formula handling for OOo 3.0 (next fall) to comply with ODFF (Calc/ODFF Implementation [OOo wiki]). Unfortunately (last I checked) ODFF had punted on this issue and did not specify any 'correct' behavior. If this is ever going to be changed, however, I think this would be the time to do it.
AOO4/LO5 • Linux • Fedora 23
Re: Sorting data numerically
That's because the file was imported into Excel as forced text (the problem didn't emerge until the file was opened in OpenOffice due to the two programs' different handling of textual numbers):Hagar de l'Est wrote:Note that in the file here, I can't manage to make any calculation at all. I just have the formulas (and they're not to be displayed in the Tools>Options configuration.
A formula entered into a cell formatted as text will always display as just a formula, not its result.badtoad1963 wrote:I originally created the file in Excel by importing a slightly modified assembly language file and kept everything formatted as text, as I wasn't planning on manipulating the data.
NB. See jrkrideau's PDF of spreadsheet problems for a brief summary of how badly both Excel & OpenOffice treat textual numbers when referred to in a formula. Issue 5658 & issue 58903.
Re: [Solved] Sorting data numerically
Interesting links huw, thanks.
View > Value Highlighting is the option I was thinking of, although it still isn't ideal.
View > Value Highlighting is the option I was thinking of, although it still isn't ideal.
AOO4/LO5 • Linux • Fedora 23
Re: [Solved] Sorting data numerically
I'm sorry to butt in, but I can't get the workaround to work. I do a search for .* and get nothing.
Are we talking about when we ctrl+a the spreadsheet>Data>Sort>columnx, then the sort shows the numbers are not arranged in 1,2,3,200 rather 1,2,200,3?
I'm really stuck here. Help?
Are we talking about when we ctrl+a the spreadsheet>Data>Sort>columnx, then the sort shows the numbers are not arranged in 1,2,3,200 rather 1,2,200,3?
I'm really stuck here. Help?
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
Re: [Solved] Sorting data numerically
The solution that worked for me is to create a temporary column and "copy" the data from the column whose data you want to sort by. Do this by entering "=VALUE(original data cell)" into the top cell of the new column and dragging it to the last cell in the column. If this doesn't make sense I will try to explain better.
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Re: [Solved] Sorting data numerically
I'll give it a shot. Thanks!
Re: [Solved] Sorting data numerically
Ok, I'm almost there, except I have 50,000+ rows. Dragging, will take some time.
However I'm not clear on what I should do after I copy the data from the original column. Do I paste it into the temp column with "=VALUE(original data cell)"?
However I'm not clear on what I should do after I copy the data from the original column. Do I paste it into the temp column with "=VALUE(original data cell)"?
-
- Posts: 12
- Joined: Sat Nov 24, 2007 9:29 pm
- Location: Winslow, Maine, USA
Re: [Solved] Sorting data numerically
Say you want to sort your data according to Column A. Create a temporary Column B. Enter into cell B1 "=VALUE(A1)". This sets the contents of cell B1 equal to the value of cell A1. No copying or pasting required. Now drag this by the corner and copy it to all the cells below and it should auto increment the contents and effectively "copy" column A to column B. As long as Column B is not formatted as text you should have it. Now replace the original Column A with the new Column B and sort!
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
Re: [Solved] Sorting data numerically
Edit > Find & ReplaceI do a search for .* and get nothing.
Search for: .*
Replace with: &
Options: Regular expressions = YES
I suspect you forgot to select that option.
AOO4/LO5 • Linux • Fedora 23
Re: [Solved] Sorting data numerically
@acknak: That is true, I forgot that part. Still this did not make it possible to sort numerically.
@badtoad1963: I get "ERR:502" on every line I drag.
I forgot to mention that the column contains text as well, like this: 54 KB. And each line contains different numbers, but same text. Maybe I'll Experiment with the format cells user-defined.
Thanks the both of you. I'm sure I'll figure this out eventually.
*Decided to post the problematic column.
@badtoad1963: I get "ERR:502" on every line I drag.
I forgot to mention that the column contains text as well, like this: 54 KB. And each line contains different numbers, but same text. Maybe I'll Experiment with the format cells user-defined.
Thanks the both of you. I'm sure I'll figure this out eventually.
*Decided to post the problematic column.
- Attachments
-
- CALC.SORT.NUM.ods
- (57.61 KiB) Downloaded 418 times
Re: [Solved] Sorting data numerically
I found a workaround the text that is in the columns containing numbers. I deleted "KB" from the column. Then I sorted it. Then I added the KB back into the column using the double quotations in the format cells properties.
Thanks all for the help.
Thanks all for the help.
Re: [Solved] Sorting data numerically
Good work! That is in fact the best solution, if you really need the "KB" displayed there. As the unit text is now part of the cell format and not the cell data, the numeric sorting will work without special steps from here on.
AOO4/LO5 • Linux • Fedora 23
- Hagar Delest
- Moderator
- Posts: 32627
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: [Solved] Sorting data numerically
That's because the content is considered as text. Select the cells, and in the Format menu, apply the Number format.abelmiah wrote:Are we talking about when we ctrl+a the spreadsheet>Data>Sort>columnx, then the sort shows the numbers are not arranged in 1,2,3,200 rather 1,2,200,3?
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10