[Solved] Problems sorting a column of numbers
Posted: 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.
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.