Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
First Value in Sorted Column is Not Sorted Properly
I am using Excel 2007 and finding that sometimes when I sort a list of values
smallest to largest it puts a value out of order in the first cell of the column of sorted values. I have even cut and pasted that record back in its right place, then perform a sort again and it goes back to the first cell. This happens whether the cell format is general, text or number. It happens when I sort from largest to smallest - the out of order value just goes to the bottom of the list. Here is part of the list I am sorting and what it looks like when I sort Smallest to Largest (A-Z): 39101613 26101101 26101102 26101103 26101105 26101106 Has anyone experienced this before? What can I do to remedy? Thank you, Jen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
First Value in Sorted Column is Not Sorted Properly
And you're sure that you're not seeing excel treat that cell as the header for
that column (and you specified that your range has headers), right? I'd bet that the value in the cell is not what you expect. Even though it may look like a number and contain nothing but digits, it could still be text. You could count the number of cells that are used in your range with a formula like: =counta(a1:a10) You could count the number of cells that hold numbers with a formula like: =count(a1:a10) Do those results match when you try them? You can test to see if a cell is numeric with: =isnumber(a1) You can test to see if a cell is text with: =istext(a1) Changing the format of a cell from Text to Number (or General) won't affect the value in the cell until you reenter it. Hitting F2, then enter is enough. If you have lots to do, you can: Select an empty cell Copy it select the range to fix Paste special|Check Values and Add Then check your sort. ======== But you should have seen a message that asked you if you wanted to sort things that look like numbers as numbers. Didn't you when you did the sort? jgraves wrote: I am using Excel 2007 and finding that sometimes when I sort a list of values smallest to largest it puts a value out of order in the first cell of the column of sorted values. I have even cut and pasted that record back in its right place, then perform a sort again and it goes back to the first cell. This happens whether the cell format is general, text or number. It happens when I sort from largest to smallest - the out of order value just goes to the bottom of the list. Here is part of the list I am sorting and what it looks like when I sort Smallest to Largest (A-Z): 39101613 26101101 26101102 26101103 26101105 26101106 Has anyone experienced this before? What can I do to remedy? Thank you, Jen -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
First Value in Sorted Column is Not Sorted Properly
Hi Dave,
I'm afraid I am suffering from a similar problem. I have changed the formatting to general and to number in an attempt to rectify the situation but neither worked. Interestingly if I do not have the rows in question surrounded by thick bold lines the largest to smallest sort function does work correctly. Could this be a glitch in the design? I realize the simple solution would then be to just put regular borders around the rows I am trying to sort however my spreadsheets have on average 30,000 lines and are seperated in varying groups by the thicker lines to identify different segments. Do you have any solutions on how to fix this particular problem? Kindest Regards, "Dave Peterson" wrote: And you're sure that you're not seeing excel treat that cell as the header for that column (and you specified that your range has headers), right? I'd bet that the value in the cell is not what you expect. Even though it may look like a number and contain nothing but digits, it could still be text. You could count the number of cells that are used in your range with a formula like: =counta(a1:a10) You could count the number of cells that hold numbers with a formula like: =count(a1:a10) Do those results match when you try them? You can test to see if a cell is numeric with: =isnumber(a1) You can test to see if a cell is text with: =istext(a1) Changing the format of a cell from Text to Number (or General) won't affect the value in the cell until you reenter it. Hitting F2, then enter is enough. If you have lots to do, you can: Select an empty cell Copy it select the range to fix Paste special|Check Values and Add Then check your sort. ======== But you should have seen a message that asked you if you wanted to sort things that look like numbers as numbers. Didn't you when you did the sort? jgraves wrote: I am using Excel 2007 and finding that sometimes when I sort a list of values smallest to largest it puts a value out of order in the first cell of the column of sorted values. I have even cut and pasted that record back in its right place, then perform a sort again and it goes back to the first cell. This happens whether the cell format is general, text or number. It happens when I sort from largest to smallest - the out of order value just goes to the bottom of the list. Here is part of the list I am sorting and what it looks like when I sort Smallest to Largest (A-Z): 39101613 26101101 26101102 26101103 26101105 26101106 Has anyone experienced this before? What can I do to remedy? Thank you, Jen -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
First Value in Sorted Column is Not Sorted Properly
Remember it's not enough to just change the numberformat of the cell.
You have to change the value, too. This was written for a different purpose (=vlookup()), but it would apply to sorting, too: Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble Aoife wrote: Hi Dave, I'm afraid I am suffering from a similar problem. I have changed the formatting to general and to number in an attempt to rectify the situation but neither worked. Interestingly if I do not have the rows in question surrounded by thick bold lines the largest to smallest sort function does work correctly. Could this be a glitch in the design? I realize the simple solution would then be to just put regular borders around the rows I am trying to sort however my spreadsheets have on average 30,000 lines and are seperated in varying groups by the thicker lines to identify different segments. Do you have any solutions on how to fix this particular problem? Kindest Regards, "Dave Peterson" wrote: And you're sure that you're not seeing excel treat that cell as the header for that column (and you specified that your range has headers), right? I'd bet that the value in the cell is not what you expect. Even though it may look like a number and contain nothing but digits, it could still be text. You could count the number of cells that are used in your range with a formula like: =counta(a1:a10) You could count the number of cells that hold numbers with a formula like: =count(a1:a10) Do those results match when you try them? You can test to see if a cell is numeric with: =isnumber(a1) You can test to see if a cell is text with: =istext(a1) Changing the format of a cell from Text to Number (or General) won't affect the value in the cell until you reenter it. Hitting F2, then enter is enough. If you have lots to do, you can: Select an empty cell Copy it select the range to fix Paste special|Check Values and Add Then check your sort. ======== But you should have seen a message that asked you if you wanted to sort things that look like numbers as numbers. Didn't you when you did the sort? jgraves wrote: I am using Excel 2007 and finding that sometimes when I sort a list of values smallest to largest it puts a value out of order in the first cell of the column of sorted values. I have even cut and pasted that record back in its right place, then perform a sort again and it goes back to the first cell. This happens whether the cell format is general, text or number. It happens when I sort from largest to smallest - the out of order value just goes to the bottom of the list. Here is part of the list I am sorting and what it looks like when I sort Smallest to Largest (A-Z): 39101613 26101101 26101102 26101103 26101105 26101106 Has anyone experienced this before? What can I do to remedy? Thank you, Jen -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep column headings from being sorted with data? | New Users to Excel | |||
How do I stop column headers from being sorted in a row? | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
my column is sorted in two sections. How do I sort entire column? | Excel Discussion (Misc queries) | |||
How attach data in a row so it can be sorted by the date column? | Excel Discussion (Misc queries) |