- Joined
- 3 Jul 2017
- Messages
- 37,205
- Likes
- 120,773
- AFL Club
- Port Adelaide
Discussion General Discussion
- Thread starter Impromptu
- Start date
- Joined
- 3 Jul 2017
- Messages
- 37,205
- Likes
- 120,773
- AFL Club
- Port Adelaide
@Beg2Differ, an XL question.
When you have a large work sheet, and are using the sort functions regularly, does XL ever choke up, and give the wrong result?
I know it is more likely me not doing it properly, but I've noticed it several times.
Most recently today.
I have a table of results I sort several times/day.
The table is only around 130 rows deep, and around 26 columns across, but is drawing data from a large spreadsheet.
The first column is just 1,2,3,4,5 ..... etc, to give me an easy way of getting it back to the starting point.
The second column is the date in reverse notation. ie todays date is 18th Feb 2022, and is represented as 220218.
Each date has 5 to 7 rows of data, that are in a specific order.
I noticed after I did my resetting sort on column 1, that the dates have 2 or 3 out of place, making the whole thing out of order. I can easily fix it, without about 5 mins work, but I was just wondering, as this has happened to me several times, is it always me that mucked it up, or has XL been known to choke on these things now and then?
Thanks in advance.
When you have a large work sheet, and are using the sort functions regularly, does XL ever choke up, and give the wrong result?
I know it is more likely me not doing it properly, but I've noticed it several times.
Most recently today.
I have a table of results I sort several times/day.
The table is only around 130 rows deep, and around 26 columns across, but is drawing data from a large spreadsheet.
The first column is just 1,2,3,4,5 ..... etc, to give me an easy way of getting it back to the starting point.
The second column is the date in reverse notation. ie todays date is 18th Feb 2022, and is represented as 220218.
Each date has 5 to 7 rows of data, that are in a specific order.
I noticed after I did my resetting sort on column 1, that the dates have 2 or 3 out of place, making the whole thing out of order. I can easily fix it, without about 5 mins work, but I was just wondering, as this has happened to me several times, is it always me that mucked it up, or has XL been known to choke on these things now and then?
Thanks in advance.
This might be helpful. It explains the logic used when sorting.
https://excel.tips.net/T003362_Fixing_Odd_Sorting_Behavior.html
https://excel.tips.net/T003362_Fixing_Odd_Sorting_Behavior.html
- Joined
- 22 Oct 2014
- Messages
- 7,247
- Likes
- 37,873
- AFL Club
- North Melb.
The short answer is yes. A sort can become unreliable if it has complex formulae and particularly if the formula refers to other cells within the sort range. What can happen is the data sorts based on what the formula returns in its current location but then recalculates to a different value if it moves location after the sort. I would have to see your file to determine if that is what is happening in this case.
If it is formulae that is causing the problem then you could test this by doing a copy/paste special as value to convert all the numbers to their underlying values and then try the sort again. If it sorts correctly doing this it will be the formulae causing the problem. Note that you don't want to save the file after converting your formulae to values because you will lose them. Maybe do this in another version of the file.
If all your cells are already values (ie not formula) then I doubt excel is getting choked up, particular with the amount of data you are talking about (which isn't a lot). It is more likely that there is something about the data that is causing the problem. It could be caused by a mix of numbers and text (that might look like numbers) in some columns. If it is a date column it could be because it is a mix of numbers formatted as dates and dates entered as text. I am guessing a bit here but these are things you could check.
Finally if you find the data sorts reliably when it is values then one approach would be to copy the range as values into a separate range/sheet each time you want to do it and then sort that range and leave the original range as is. That is a little bit inconvenient but better than fixing your data each time it sorts incorrectly.
If it is formulae that is causing the problem then you could test this by doing a copy/paste special as value to convert all the numbers to their underlying values and then try the sort again. If it sorts correctly doing this it will be the formulae causing the problem. Note that you don't want to save the file after converting your formulae to values because you will lose them. Maybe do this in another version of the file.
If all your cells are already values (ie not formula) then I doubt excel is getting choked up, particular with the amount of data you are talking about (which isn't a lot). It is more likely that there is something about the data that is causing the problem. It could be caused by a mix of numbers and text (that might look like numbers) in some columns. If it is a date column it could be because it is a mix of numbers formatted as dates and dates entered as text. I am guessing a bit here but these are things you could check.
Finally if you find the data sorts reliably when it is values then one approach would be to copy the range as values into a separate range/sheet each time you want to do it and then sort that range and leave the original range as is. That is a little bit inconvenient but better than fixing your data each time it sorts incorrectly.
Have been caught out so many times and spent many hours fixing it .... when I say many .. I'm taking days !!
- FWIW, I always now save my original copy and play with another until happy with the results ..
- Not that I don't understand what happens .... just that I sometimes forget the finer details ..
- FWIW, I always now save my original copy and play with another until happy with the results ..
- Not that I don't understand what happens .... just that I sometimes forget the finer details ..
- Joined
- 3 Jul 2017
- Messages
- 37,205
- Likes
- 120,773
- AFL Club
- Port Adelaide
- Joined
- 3 Jul 2017
- Messages
- 37,205
- Likes
- 120,773
- AFL Club
- Port Adelaide