[Solved] Problems sorting a column of numbers

Discuss the spreadsheet application

[Solved] Problems sorting a column of numbers

Postby i75Dave » Tue Oct 03, 2017 6:40 pm

When working in Calc, I sometimes run into a problem with sorting numbers.

For example, I set up a column for housing the month of many accounting transactions - Jan = 1, Feb = 2, etc. Data in this column can originate from various sources such as keyboard entry or copied in and combined from another Calc spreadsheet. But sometimes they do not sort down in a logical numerical sequence but appear to split into 2 groups of logical sort ... I suspect some cells may be numeric values while others are text and this may be causing the problem of two sorted groups in the column.

I have tried reformatting the entire column as "Numeric" or as "Text," and then attempting to sort again . . . with no success.

My work-around has been to open 2 adjacent columns to the right, and use "=value(cell reference)" in the first new column . . . and then Copy and perform a "Paste Special" into the next. This column now sorts correctly. But this is time consuming especially when manipulating a lot of dated (3 columns yyyy mm dd) of financial data.

Can anybody throw any light on why the original column will not sort correctly? Is it a cell's text/value property problem? Is there a way of checking a cell's properties to determine its status?

I could continue to use my workaround but would prefer to have a better understanding of why the sort is not successful in the first place.

Thanks for any thoughts about this,

Dave.
Last edited by i75Dave on Tue Oct 03, 2017 7:25 pm, edited 1 time in total.
OpenOffice 4.1.1 on Win 7 64bit
i75Dave
 
Posts: 12
Joined: Thu Mar 12, 2015 9:41 pm

Re: Problems sorting a column of numbers

Postby MrProgrammer » Tue Oct 03, 2017 6:43 pm

i75Dave wrote:I suspect some cells may be numeric values while others are text and this may be causing the problem of two sorted groups in the column.
i75Dave wrote:Is there a way of checking a cell's properties to determine its status?
Read section 1. Types of data in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3077
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Problems sorting a column of numbers

Postby RoryOF » Tue Oct 03, 2017 6:44 pm

To check a cell's content, use /View /Value highlighting: numbers will be blue, text black and results of formulae green.
Apache OpenOffice 4.1.4 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25089
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Problems sorting a column of numbers

Postby i75Dave » Tue Oct 03, 2017 7:23 pm

Dear RoryOF,
Many thanks for your quick reply. A perfect answer. I applied the /View/Value test and as suspected, found I had a mixture of text and values.
Incidentally, with apologies to Pooh, October 14th is also the anniversary of the Battle of Hastings ... the infamous date when we all had to learn to speak French and behave ourselves for the next 400 years! Dave. ;)
OpenOffice 4.1.1 on Win 7 64bit
i75Dave
 
Posts: 12
Joined: Thu Mar 12, 2015 9:41 pm


Return to Calc

Who is online

Users browsing this forum: Yahoo [Bot] and 47 guests