You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by David Deeks <pr...@gmail.com> on 2022/07/01 18:20:08 UTC
Calc data sorting problem - cell formatting appears same but mustn't be?
Hi there all
I am using Calc on a MacBook Pro running OS High Sierra 10.13.6 (the highest version this Mac will run).
A long while back I had a problem sorting data on Calc spreadsheets which boiled down to cell formatting being different, solved by a friendly Brian if I remember correctly!
But I have now had the problem occur again with the cell formatting appearing to be consistent. I am attaching a copy of a spreadsheet with data sorted by column I. The cells in this column all identify their category/format as 'Text/@' and I have not to my knowledge changed anything. Note however that from row 1813 the sort begins again - I have highlighted these rows in orange for clarity.
In case it is significant, note that column J shows the year of input and indicates that most of these are this year - although a couple are 2014. There are other column J 2022 entries on the spreadsheet however, where column I sorts correctly.
Column J is affected by the same problem. I have again highlighted in orange the cells in this column that must be a different format to the majority, so that they can easily be found - see the high incidence alongside the column I examples which might be significant, but there are many more throughout the spreadsheet e.g. rows 370-373.
I have tried reformatting these ‘rogue' cells. I have tried a blanket reformat of both columns. I have tried copying and pasting from the majority ones to the rogue ones on the assumption that this would carry over the correct formatting, and then re-entering the correct data. But amazingly (to me!) the problem persists.
I have finally tried searching for this problem on the website, without success.
Can anyone who is much more technical than me, help? Apols if f I have missed any useful info, please let me know.
Thanks in advance.
David Deeks
Re: Calc data sorting problem - cell formatting appears same but mustn't be?
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 19:20 01/07/2022 +0100, David Deeks wrote:
>I am using Calc on a MacBook Pro running OS High Sierra 10.13.6 (the
>highest version this Mac will run). A long while back I had a
>problem sorting data on Calc spreadsheets which boiled down to cell
>formatting being different, solved by a friendly Brian if I remember correctly!
Could that be me?!
>But I have now had the problem occur again with the cell formatting
>appearing to be consistent. I am attaching a copy of a spreadsheet
>with data sorted by column I. The cells in this column all identify
>their category/format as 'Text/@' and I have not to my knowledge
>changed anything. Note however that from row 1813 the sort begins
>again - I have highlighted these rows in orange for clarity.
Although the cell formatting is indeed Text, the majority of the
values in column I are actually numbers. You can see that fairly
simply by selecting the column and then going to Format | Cells... |
Alignment | Text alignment | Horizontal and selecting Default from
the drop-down menu. Your black numbers will show as right-aligned,
with your orange text values left-aligned. I'm guessing that the
black values were entered into cells that were formatted as Number,
the column was then formatted as Text, and the orange values entered
after this. Note that Text formatting will ensure that entered values
are saved as text, but will not change the actual contents of cells
already occupied, so your numbers stay as numbers.
A simple way forward would be to convert all the values in column I
to numbers - thus changing your orange values from text values to
numerical values. An easy way to do this is to select the column and
use Data | Text to Columns... | OK. But note that you will need to
deal with the rogue value in I1812 first, or it will scupper the
effect. The you can sort column I as you wish.
>I have tried reformatting these 'rogue' cells. I have tried a
>blanket reformat of both columns.
Note that changing format does not change the actual contents of cells.
I think there are also some strange effects caused by cell protection
which may be causing confusion.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Calc data sorting problem - cell formatting appears same but mustn't be?
Posted by David Deeks <pr...@gmail.com>.
Well Brian, here you go again - yes, ’twas indeed you last time!
Thanks so much. A bit late tonight now for an oldie to try out your suggestions, but will certainly aim to do so tomorrow!
All best
David
> On 1 Jul 2022, at 21:25, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 19:20 01/07/2022 +0100, David Deeks wrote:
>> I am using Calc on a MacBook Pro running OS High Sierra 10.13.6 (the highest version this Mac will run). A long while back I had a problem sorting data on Calc spreadsheets which boiled down to cell formatting being different, solved by a friendly Brian if I remember correctly!
>
> Could that be me?!
>
>> But I have now had the problem occur again with the cell formatting appearing to be consistent. I am attaching a copy of a spreadsheet with data sorted by column I. The cells in this column all identify their category/format as 'Text/@' and I have not to my knowledge changed anything. Note however that from row 1813 the sort begins again - I have highlighted these rows in orange for clarity.
>
> Although the cell formatting is indeed Text, the majority of the values in column I are actually numbers. You can see that fairly simply by selecting the column and then going to Format | Cells... | Alignment | Text alignment | Horizontal and selecting Default from the drop-down menu. Your black numbers will show as right-aligned, with your orange text values left-aligned. I'm guessing that the black values were entered into cells that were formatted as Number, the column was then formatted as Text, and the orange values entered after this. Note that Text formatting will ensure that entered values are saved as text, but will not change the actual contents of cells already occupied, so your numbers stay as numbers.
>
> A simple way forward would be to convert all the values in column I to numbers - thus changing your orange values from text values to numerical values. An easy way to do this is to select the column and use Data | Text to Columns... | OK. But note that you will need to deal with the rogue value in I1812 first, or it will scupper the effect. The you can sort column I as you wish.
>
>> I have tried reformatting these 'rogue' cells. I have tried a blanket reformat of both columns.
>
> Note that changing format does not change the actual contents of cells.
>
> I think there are also some strange effects caused by cell protection which may be causing confusion.
>
> I trust this helps.
>
> Brian Barker
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 17:26 04/07/2022 +0100, David Deeks wrote:
>ATTN Brian Barker?
I trust others are permitted to offer assistance?
>Here is where I have hit the problem. I am
>attaching a copy of the full spreadsheet as it
>usually is, sorted on columns A,I,B ...
>
and the same spreadsheet but with column I
>converted using "Text to columns". If you go to
>the bottom you will see that there is one more
>row at the end of column I, and the values in
>the rows above are one row below where they should be.
I dealt with this issue in my last message. The
"Text to Columns" trick will work for both your
four-digit numbers and text, but goes awry for
your rogue value which was in I1812. There you
had "*2011 **1996" with a line break separating
the two parts. "Text to Columns" divides this
into two rows of the column - not what you need.
As I said, you will have to deal with this first
- perhaps by deleting the value and reinserting
it after the conversion. But in any case, how do
you wish or expect this value to sort amongst
other values in Column I anyway? Note that this
rogue value has moved to I1241 in your latest version of the spreadsheet.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 23:15 05/07/2022 +0100, David Deeks wrote:
>Sorry Brian, I was just being courteous - I thought if I included
>everyone with my follow-up query it would look like I was doubting
>your expertise!
Oh, it's perfectly OK to doubt my expertise.
>... everything now seems to be hunky dory.
Good-oh!
>So "text to columns" converts to text those cells originally entered
>as numbers?
No, rather the other way around: your text values are converted to numbers.
The normal use of Text to Columns... is to divide text in one column
into multiple columns. So if you have names such as "David Deeks" in
one column and you use Text to Columns... with Space ticked, you will
get "David" in the original column and "Deeks" in the next column to
the right (with the intervening space helpfully swallowed up). But in
the process, it appears that values are reinterpreted, just as they
would be if the data was entered afresh. So a text value "1000" is
treated as if you had typed 1000, in other words as the number one
thousand, not the four text characters.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by "Thats only me." <am...@aol.com.INVALID>.
Very joyful.
Sent from the all new AOL app for Android
On Fri, Jul 8, 2022 at 0:01, David Deeks<pr...@gmail.com> wrote: Great explanation Brian - a feature I’d never really understood before so just ignored, certainly an ingenious use of it on this occasion, may well come back to it in future for its intended purpose!
Thanks again for your help.
Doubt your expertise? Never!
All best
David
> On 7 Jul 2022, at 03:57, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 23:15 05/07/2022 +0100, David Deeks wrote:
>> Sorry Brian, I was just being courteous - I thought if I included everyone with my follow-up query it would look like I was doubting your expertise!
>
> Oh, it's perfectly OK to doubt my expertise.
>
>> ... everything now seems to be hunky dory.
>
> Good-oh!
>
>> So "text to columns" converts to text those cells originally entered as numbers?
>
> No, rather the other way around: your text values are converted to numbers.
>
> The normal use of Text to Columns... is to divide text in one column into multiple columns. So if you have names such as "David Deeks" in one column and you use Text to Columns... with Space ticked, you will get "David" in the original column and "Deeks" in the next column to the right (with the intervening space helpfully swallowed up). But in the process, it appears that values are reinterpreted, just as they would be if the data was entered afresh. So a text value "1000" is treated as if you had typed 1000, in other words as the number one thousand, not the four text characters.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by David Deeks <pr...@gmail.com>.
Great explanation Brian - a feature I’d never really understood before so just ignored, certainly an ingenious use of it on this occasion, may well come back to it in future for its intended purpose!
Thanks again for your help.
Doubt your expertise? Never!
All best
David
> On 7 Jul 2022, at 03:57, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 23:15 05/07/2022 +0100, David Deeks wrote:
>> Sorry Brian, I was just being courteous - I thought if I included everyone with my follow-up query it would look like I was doubting your expertise!
>
> Oh, it's perfectly OK to doubt my expertise.
>
>> ... everything now seems to be hunky dory.
>
> Good-oh!
>
>> So "text to columns" converts to text those cells originally entered as numbers?
>
> No, rather the other way around: your text values are converted to numbers.
>
> The normal use of Text to Columns... is to divide text in one column into multiple columns. So if you have names such as "David Deeks" in one column and you use Text to Columns... with Space ticked, you will get "David" in the original column and "Deeks" in the next column to the right (with the intervening space helpfully swallowed up). But in the process, it appears that values are reinterpreted, just as they would be if the data was entered afresh. So a text value "1000" is treated as if you had typed 1000, in other words as the number one thousand, not the four text characters.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by David Deeks <pr...@gmail.com>.
> On 5 Jul 2022, at 21:39, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 17:26 04/07/2022 +0100, David Deeks wrote:
>> ATTN Brian Barker?
>
> I trust others are permitted to offer assistance?
Sorry Brian, I was just being courteous - I thought if I included everyone with my follow-up query it would look like I was doubting your expertise!
>
>> Here is where I have hit the problem. I am attaching a copy of the full spreadsheet as it usually is, sorted on columns A,I,B ...
>>
> and the same spreadsheet but with column I
>> converted using "Text to columns". If you go to the bottom you will see that there is one more row at the end of column I, and the values in the rows above are one row below where they should be.
>
> I dealt with this issue in my last message. The "Text to Columns" trick will work for both your four-digit numbers and text, but goes awry for your rogue value which was in I1812. There you had "*2011 **1996" with a line break separating the two parts. "Text to Columns" divides this into two rows of the column - not what you need. As I said, you will have to deal with this first - perhaps by deleting the value and reinserting it after the conversion. But in any case, how do you wish or expect this value to sort amongst other values in Column I anyway? Note that this rogue value has moved to I1241 in your latest version of the spreadsheet.
My fault entirely with this one Brian. Firstly I attached the wrong version of my ‘text to columns’ spreadsheet. I had in fact made the corrections you had highlighted on the version I should have attached, but still with the problem I described. My second mistake however was not to realise that as well as cells containing two years, cells containing one year followed by a single asterisk were also evidently unacceptable and led to strange effects - because having found several of these and removed the asterisks, everything now seems to be hunky dory.
So ‘text to columns’ converts to text those cells originally entered as numbers?
Thanks so much again and apols for the above. I promise not to single you out for any more help I may need in future - but I really have appreciated it.
All best
David
>
> I trust this helps.
>
> Brian Barker
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
ATTN Brian Barker? - cell reformatting - have hit a snag!
Posted by David Deeks <pr...@gmail.com>.
Hi there again Brian
Have stumbled on a bit of a snag. Am hoping you can help! - see below ….
> On 1 Jul 2022, at 21:25, Brian Barker <b....@btinternet.com.INVALID> wrote:
>
> At 19:20 01/07/2022 +0100, David Deeks wrote:
>> I am using Calc on a MacBook Pro running OS High Sierra 10.13.6 (the highest version this Mac will run). A long while back I had a problem sorting data on Calc spreadsheets which boiled down to cell formatting being different, solved by a friendly Brian if I remember correctly!
>
> Could that be me?!
Per my previous reply, indeed it was!
>
>> But I have now had the problem occur again with the cell formatting appearing to be consistent. I am attaching a copy of a spreadsheet with data sorted by column I. The cells in this column all identify their category/format as 'Text/@' and I have not to my knowledge changed anything. Note however that from row 1813 the sort begins again - I have highlighted these rows in orange for clarity.
>
> Although the cell formatting is indeed Text, the majority of the values in column I are actually numbers. You can see that fairly simply by selecting the column and then going to Format | Cells... | Alignment | Text alignment | Horizontal and selecting Default from the drop-down menu. Your black numbers will show as right-aligned, with your orange text values left-aligned. I'm guessing that the black values were entered into cells that were formatted as Number, the column was then formatted as Text, and the orange values entered after this. Note that Text formatting will ensure that entered values are saved as text, but will not change the actual contents of cells already occupied, so your numbers stay as numbers.
>
I didn’t realise the significance of column v cell formatting - a bit of a revelation! Or that changing format doesn’t change the actual cell contents (re your further comment below).
> A simple way forward would be to convert all the values in column I to numbers - thus changing your orange values from text values to numerical values. An easy way to do this is to select the column and use Data | Text to Columns... | OK. But note that you will need to deal with the rogue value in I1812 first, or it will scupper the effect. The you can sort column I as you wish.
Here is where I have hit the problem. I am attaching a copy of the full spreadsheet as it usually is, sorted on columns A,I,B ...