x.ods (17.0 KB)
All I want to do is sort this file by sym and date. It gets the sym but not the date. I think it’s a hidden apostrophe. I checked a cell in format and text to column and I can not see it. Cell protection didn’t do it. Changing date to text or number format didn’t do it either. What have I not learned yet that is causing the problem?
@lunchboxes,
I see your file correctly sorted (first by sym, then by date).
If do you want to sort by date and sym, just swap sort keys.
Or add to your question (or in a comment) a reduced sample of what do you expect. Thanks.
The date column is wrong. It is sorted by dates since 6/1 and then all dates previous to 6/1. I would like to see the dates from oldest to newest in order.
Maybe you must change date format from MM/DD/YY to DD/MM/YY (as my screenshot).
@LeroyG It is not a date issue. When Sym column is sorted first then “COP” is sorted before "COP " (COP+Space). The spaces need to be trimmed first. Cheers, Al
I see now that the problem began with PBT, not with COP (that was what I tested with).
I just tried the summary for my problems and it works. I want to thank everybody that responded. I am just a beginner with spreadsheets. Thanks!
Hi, have a look at the attached, some of the sym cell values have a blank character on the end, I have added a column showing the length of the sym cell so you can see the offenders, you need to remove the blank character and your sort should work. The dates look fine.
Let us know if it helps.
Windows 10 home, LO 7.3.4.2
LOQ_20220615.ods (38.4 KB)
No! especially Colums E & I are messed up by mixed use of Decimal-point and …-Comma
Whether you see comma or another decimal separator for numeric values depends on your locale. However, cells in E2:E5 have text content, E6:E13 numeric, E14:E20 text, E21:E28 numeric, … can be easily checked with View → Value Highlighting (Ctrl+F8), numeric is blue, text is black, formula results are green.
Do you mean this? I spent 20 minutes watching videos and came up with this. I clicked on the sym column, then data, then text to column. The column looked ok to me but I clicked on trim spaces and I think it worked.
There is a lot of advice here. Let’s put it all together:
First click View > Value Highlighting to see where the issues lie
Select column A Sym only then click Data > Text to columns and make sure you tick the box Trim spaces and then OK. This will remove the spaces and make all the “COP” sort the same
The percentages being a mixture of text and numbers bugs me.
Select both columns, select E then Crl+click column I . Click Format > Cells. In the dialogue select the tab Numbers and under Categories select Percent in the right hand column select -12.95% (or similar number, I chose one with leading zero) and OK
Keeping both columns selected click Edit > Find and Replace (Ctrl+H)
.+
$0
Now you can Sort your data correctly. You can also toggle View > Value highlighting off
result
x_EAl-78622.ods (18.1 KB)
You might find this page particularly useful, Frequently asked questions - Calc - The Document Foundation Wiki
Cheers, Al
Hello lunchboxes,
the reason is that the data in column sym is not consistent. Sometimes there is a space behind the entry. This interferes with sorting. You can easily correct this by marking the column and defining the data as text again under “Text in columns” and then trimming the spaces. The data is now unified and can be sorted.
dscheikey
dscheikey
How do you see the space?
Simply enter the cell, and put the cursor after the end of the text. You will see that for the Sym cells in the beginning, the cursor puts after the P without a space; and in those that are sorted incorrectly after, the cursor appears after a space.
Basically, that is what Writer’s “show formatting marks” is for, but there’s nothing like that in Calc, so you need to inspect the data yourself carefully there.
@lunchboxes
I can’t see the spaces too. At first it was just a guess that the problem could be explained by additional invisible spaces.
I then made an analysis of the data in column A in an auxiliary column. With =LEN(A2), any blank space is also counted. After copying the formula up to line 44, it quickly became clear that this was the problem.
You could also make the spaces a little more visible by setting the alignment of the cell contents to right-justified for a short time. This way you can also see which cells do not fit.
Turn Data>Autofilter on and click the “Sym” drop-down.
There are entries
COP
"COP "
PBT
"PBT "
and so on
=LEN(A2)-LEN(TRIM(A2)) reports the difference for each entry
Hi Karolus,
The file I tested has column E heading as %Chg, and column I heading as 52W %Low, no dates involved, the only column that had dates was column J, and they look fine.
Regards
@gregors15:
Please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead. Thanks.
Yes, column J is the only one with dates.
Tomorrow I will try to figure out column E. I try to format the column and then the apostrophe shows up. I hate that thing. I would like to format the column to percentages.
My data supplier changed on 6/1 That’s when the trouble started.
