[Solved] Sorting data numerically

Discuss the spreadsheet application
Post Reply
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

[Solved] Sorting data numerically

Post by clark.leach »

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... :?
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sorting data numerically

Post by Hagar Delest »

Is your reference a number or text ?
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
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Sorting data numerically

Post by TerryE »

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.
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: Sorting data numerically

Post by clark.leach »

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
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Sorting data numerically

Post by huw »

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.
This sounds great but I couldn't get this trick to work Terry. Have I understood correctly?

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
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sorting data numerically

Post by Hagar Delest »

huw wrote:This sounds great but I couldn't get this trick to work Terry. Have I understood correctly?
+1.
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
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Sorting data numerically

Post by TerryE »

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:
  • 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
Do you see the difference? Excel will interpret a text field in arithmetic as a number if its value is a number and skip if not. Calc just skips it if it is not a number type; end of story.

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.
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: Sorting data numerically

Post by clark.leach »

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
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sorting data numerically

Post by Hagar Delest »

badtoad1963 wrote:What does "format cells" do if not equivalent to what I just did manually?
+1.
Note that I also tried to Find and Replace the apostrophe (that tags the content as text) but it doesn't work :evil:
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Sorting data numerically

Post by TerryE »

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
  • 13 Bytes if A1='12
    #VALUE! if A1=abc
Now this is where calc start to get bizarre. Under normal circumstance string cell values are not coerced but evaluated as 0, so (A1+1)&" Bytes" returns
  • 1 Bytes if A1='12
    1 Bytes if A1=abc
However, string expressions are coerced under some circumstances, so ((A1&"")+1)&" Bytes" returns
  • 13 Bytes if A1=12
    13 Bytes if A1='12
    Err:502 if A1=abc
So now I am wiser, but not enlightened
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
fst
Volunteer
Posts: 152
Joined: Wed Nov 28, 2007 2:31 pm

Re: Sorting data numerically

Post by fst »

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
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Sorting data numerically

Post by huw »

badtoad1963 wrote:That trick seems to work. But what a pain in the posterior.
Presumably, you used the trick in Excel?
Edit: Answerd by PM
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.
which was TerryE's helper column suggestion, directly above badtoad1963's post - I just wasn't reading the thread thoroughly.
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:
You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.
Last edited by huw on Thu Nov 29, 2007 11:00 am, edited 1 time in total.
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Sorting data numerically

Post by TerryE »

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.
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sorting data numerically

Post by Hagar Delest »

huw wrote:You don't "Search for" the apostrophe, you search for ^. or .*, and replace with ampersand.
Thanks huw and Frank !
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
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

I'm not sure but I think I'm sorry I asked :roll: :)
C Leach
Aut viam inveniam aut faciam
OOo 3.0.X on Ms Windows XP + Ubuntu Linux
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

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.
AOO4/LO5 • Linux • Fedora 23
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Sorting data numerically

Post by huw »

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.
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):
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.
A formula entered into a cell formatted as text will always display as just a formula, not its result.

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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

Interesting links huw, thanks.

View > Value Highlighting is the option I was thinking of, although it still isn't ideal.
AOO4/LO5 • Linux • Fedora 23
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

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?
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

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
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

I'll give it a shot. Thanks!
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

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)"?
clark.leach
Posts: 12
Joined: Sat Nov 24, 2007 9:29 pm
Location: Winslow, Maine, USA

Re: [Solved] Sorting data numerically

Post by clark.leach »

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
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

I do a search for .* and get nothing.
Edit > Find & Replace
Search for: .*
Replace with: &
Options: Regular expressions = YES

I suspect you forgot to select that option.
AOO4/LO5 • Linux • Fedora 23
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

@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.
Attachments
CALC.SORT.NUM.ods
(57.61 KiB) Downloaded 418 times
abelmiah
Posts: 12
Joined: Wed Jan 23, 2008 2:33 am

Re: [Solved] Sorting data numerically

Post by abelmiah »

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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Sorting data numerically

Post by acknak »

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
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] Sorting data numerically

Post by Hagar Delest »

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?
That's because the content is considered as text. Select the cells, and in the Format menu, apply the Number format.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Post Reply