[Solved] Sorting doesn't work fully

[Skompy]

Is there a problem or issue with the sort function in OO Calc?

I have a large multi-page workshseet, with one of the pages having over 12000 rows and 44 columns.
When I use the sort function on this page, I do not get the results I am expecting - the first half or so of the sort results are fine but after about about 15-20 rows, the sort shows results further down the table that should be at the top of the results.

So I am wondering if there is a limit to the extent of the sort function, perhaps by the number of rows or columns in a worksheet.

I also wondered if there was a way to sort by more than 3 columns in Calc.

[FJCC]

I just sorted 13,000 rows and 48 columns without a problem. A possible reason for what you are seeing is that some cells are text and some are numbers. If you select the menu View -> Value Highlighting, numbers will be blue, text will be black and cells with formulas will be green. Is everything as you expect?

The sorting algorithm is stable, so you can sort in a kind of reverse order if you need to sort by more than three columns. To sort by C, F, B, E, A, C, first sort by E, A, C, then by C, F, B. Always make a back copy of your file when experimenting with sorting!

[John_Ha]

I doubt there is a problem with Calc - it is far more likely there is a problem with your spreadsheet.

Do some simple tests to prove it.

Do the cells you are sorting contain formulae which reference other rows - if so check to see what is happening because they will not sort correctly. See the Caution! on page 321 of the Calc Guide.

[Skompy]

Is everything as you expect?

No. There are some cells which are black but I have no idea how that has happened.
I have many rows in my spreadsheet so is there a bulk way of changing all of those that are text into numeric?

I am finding lots of cells that have a ' before the number. I do not see this though while it is formatted as text (@). Only after I use the format painter to copy the format from one of the other cells that are numeric does this apostrophe show up.

So is there a bulk way to clear up this problem?

[Sliderule]

So is there a bulk way to clear up this problem?"

Yes there is.

If you want to apply a numerical format to a column of numbers in text format (for example, text "000123" becomes number "123"), do the following:

1. Select the column in which the digits are found in text format. Set the cell format in that column as "Number".
2. Choose Edit - Find & Replace
3. In the Search for box, enter ^[0-9]
4. In the Replace with box, enter &
5. Check Regular expressions
6. Check Current selection only
7. Click Replace All

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[FJCC]

This most commonly happens when data are imported from a csv file or via copy/paste. Either the number formatting confuses Calc or there are leading or trailing blanks. What exactly is in these cells?
You can check for extra blank characters by clicking on one of the cells that is unexpectedly text and then pressing F2. You can use the left and right arrow keys to step through the cell content.
The general procedure for fixing this, if the problem is the format, is to select the column and set the format, using the menu Format -> Cells, to something appropriate for the data type, Then, with the column still selected, go to the menu Data -> Text to Columns. In the Separator section, choose a character that does not appear in any cell, probably the tab. Press Accept and the data will be re-entered. The cell format you chose should help Calc interpret the input correctly. With more information, I might be able to give more specific advise.

[Skompy]

I hope this helps, please be sure to let me / us know.

It hasn't worked. There are still many cells that are black when I select the value highlighting option.

[Skompy]

The general procedure for fixing this, if the problem is the format, is to select the column and set the format, using the menu Format -> Cells, to something appropriate for the data type, Then, with the column still selected, go to the menu Data -> Text to Columns. In the Separator section, choose a character that does not appear in any cell, probably the tab. Press Accept and the data will be re-entered. The cell format you chose should help Calc interpret the input correctly. With more information, I might be able to give more specific advise.

I used your suggestion above and it seems to have worked. I am not 100% sure if it has as I have 12000+ rows, but from paging down many times, I cannot see any black numbers. Also, when I saved the file, it saved much quicker than it does nornmally. I wonder why that is so.
Many thanks for helping me with this!

This most commonly happens when data are imported from a csv file or via copy/paste. Either the number formatting confuses Calc or there are leading or trailing blanks. What exactly is in these cells?

The numbers in the 2 columns in question are finishing position in a race and the number of runners. These have all been manually entered by me, direct from the keyboard. No copying and pasting either. Puzzled by how these cells became text.

[Skompy]

I have just added a new row of data and when I entered the finishing position and number of runners in the pertinent columns, they came up as black not blue as I was expecting. The row was inserted in between two other rows.
Do you know why this would be? Is there some default setting that is resulting in this?

UPDATE
Added another couple of rows, in different parts of the main page and the above numbers issue didn't appear. Numbers appeared as numerical.

[Alex1]

I used your suggestion above and it seems to have worked. I am not 100% sure if it has as I have 12000+ rows, but from paging down many times, I cannot see any black numbers.

You can select a column and rightclick the status bar left from the scale slider and select Count and then CountA and compare the results. They should differ by the number of cells that are meant to contain text.

[FJCC]

If you are typing in pure numbers like 1 or 13 and the cells above and below the insertion are formatted as numbers, then I can't account for an inserted cell being formatted as text. What happens if you insert a row then, before entering any data, you select the cell and then the menu Format -> Default Format. Does number entry then work as expected.
Without seeing an example file, this is going to be tough to diagnose.

[Skompy]

You can select a column and rightclick the status bar left from the scale slider and select Count and then CountA and compare the results. They should differ by the number of cells that are meant to contain text.

I don't know what a scale slider is. I use a Mac and so perhaps that is a Windows version reference.

[Skompy]

What happens if you insert a row then, before entering any data, you select the cell and then the menu Format -> Default Format. Does number entry then work as expected.

The numbers I entered were formatted as numbers (General) after doing the above.

I am perplexed as to why the problem occurred once but then not again after it seemed that the values had all been changed to numeric with your solution above.
With the Value Highlighting feature left on all the time now, I will be able to see if this occurs again in the future and perhaps try to see why.
I will check that the values of the pertinent cells in the row above and the row below the newly inserted row are formatted as numbers.

[robleyd]

It seems you may be unfamiliar with some aspects of how spreadsheets work; you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know, especially 2 and 5

OpenOffice Spreadsheet Tutorial for Beginners with Examples

[John_Ha]

Upload an example file here on on a file share site - how do you expect us to guess exactly what you are doing wrong?

Have you read the manual? Or my earlier post?

[robleyd]

I don't know what a scale slider is. I use a Mac and so perhaps that is a Windows version reference.

On the Status Bar at the bottom right of your document window. This is common to AOO on Mac, Linux and Windows.

[Skompy]

I don't know what a scale slider is. I use a Mac and so perhaps that is a Windows version reference.

On the Status Bar at the bottom right of your document window. This is common to AOO on Mac, Linux and Windows.

statusbar.png

I have never used anything at the bottom of the spreadsheet, never mind know what the features/areas are called. But glad to learn this now.
OK, so I ran the count and countA commands and I got a difference of 1 in the totals of one column. How can I search for the cell that has a text cell in that column?

[Skompy]

It seems you may be unfamiliar with some aspects of how spreadsheets work; you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know, especially 2 and 5

OpenOffice Spreadsheet Tutorial for Beginners with Examples

No, I am not unfamiliar with how spreadsheets work and I have been using them for many years.
I may not know some aspects of them but not most of the elementary things. Like most people I imagine, I have learned what I need to for the tasks that I needed to do.

[Skompy]

Have you read the manual?

Manuals are no longer supplied by software companies. This has been so for many years.
I also find it hard to learn things via text on a screen, save for very simple or easy to follow steps.
And also, I need a teacher present for complex or abstract things, as I always have questions, etc.

[Skompy]

I doubt there is a problem with Calc - it is far more likely there is a problem with your spreadsheet.

Do some simple tests to prove it.

Do the cells you are sorting contain formulae which reference other rows - if so check to see what is happening because they will not sort correctly. See the Caution! on page 321 of the Calc Guide.

No, there are no formulas in the sort range.
And yes, I did read this post but I was following the solution laid out by someone else, so I did not respond at the time.
I have also explained why I will not upload a file here.

[RoryOF]

Have you read the manual?

Manuals are no longer supplied by software companies. This has been so for many years.

John_Ha has given you a pointer to the manual, any relevant section of which you can print out if you don't like reading it on-screen.

[Villeroy]

Convert all values of a column to text?

Select the cells in question and format as text (number format code @)

Option #1
=IF(ISNUMBER(A1);TEXT(A1;"@");A1) [copy down and then paste-special text]
Instead of the "@" you can enter some other number format code.

Option #2
Edit>Find/Replace...
[More Options]
[X] Current selection
[X] Regular expressions
Search: .+ (this is a dot and a plus)
Replace: & (ampersant)
[Replace all]

Option #3:
Data>Text to columns
no separator
[OK]

Options #2 and #3 effectively re-enter all values into the cells that are formatted as text

[Skompy]

Convert all values of a column to text?

Select the cells in question and format as text (number format code @)

Option #1
=IF(ISNUMBER(A1);TEXT(A1;"@");A1) [copy down and then paste-special text]
Instead of the "@" you can enter some other number format code.

Option #2
Edit>Find/Replace...
[More Options]
[X] Current selection
[X] Regular expressions
Search: .+ (this is a dot and a plus)
Replace: & (ampersant)
[Replace all]

Option #3:
Data>Text to columns
no separator
[OK]

Options #2 and #3 effectively re-enter all values into the cells that are formatted as text

I don't understand why you have posted this. I have no need to convert all values of a column to text.

[Skompy]

Have you read the manual?

Manuals are no longer supplied by software companies. This has been so for many years.

John_Ha has given you a pointer to the manual, any relevant section of which you can print out if you don't like reading it on-screen.

I can find no pointer. He mentions the Calc Guide, but not where to get it from.

[Villeroy]

OK, then you want to convert all values to numbers in order to get correct sort orders.

Select the cells in question and format as anything but text. Number format "General" will do.

Option #1
=VALUE(A1)
copy down and then paste-special text

The other options work in the same manner. They re-entter all data into the preformatted context.

I can find no pointer. He mentions the Calc Guide, but not where to get it from.

http://lmgtfy.com/?q=Calc+Guide

[RoryOF]

And you cannot enter "OpenOffice Calc guide" into a search engine?

Try
https://wiki.openoffice.org/w/images/b/ ... cGuide.pdf
and
https://wiki.openoffice.org/wiki/Docume ... Guide/Calc

[Skompy]

And you cannot enter "OpenOffice Calc guide" into a search engine?

Try
https://wiki.openoffice.org/w/images/b/ ... cGuide.pdf
and
https://wiki.openoffice.org/wiki/Docume ... Guide/Calc

Of course, but when I did so, from the results I got, I clicked on the first result and the link just hung. I tried a few times too.
The first link you have provided is to an older version of the program, but I have the link to the guide now.

Also, if you are trying to help someone, getting irritated/uppity is not a good way to approach helping.

[robleyd]

Also, if you are trying to help someone, getting irritated/uppity is not a good way to approach helping.

Also good advice to those seeking help.