If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel Sort Doesn't Work
I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP
5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. |
#2
|
|||
|
|||
Excel Sort Doesn't Work
Good chance that Excel sees the "numbers" as text, especially if copied from
somewhere else. Select the numbers and DataText to ColumnsFinish Try the sort again. Gord Dibben MS Excel MVP On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut wrote: I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP 5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. |
#3
|
|||
|
|||
Excel Sort Doesn't Work
On Aug 22, 3:06 pm, Gord Dibben gorddibbATshawDOTca wrote:
Good chance that Excel sees the "numbers" as text, especially if copied from somewhere else. Select the numbers and DataText to ColumnsFinish Try the sort again. Gord Dibben MS Excel MVP On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut wrote: I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP 5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. Thank you for your reply. Unfortunately that did not work. I should have mentioned that the data was in column I. I obtained the first element with =$C2-$B2 and dragged the cursor down to get the remaining elements in the column from the values in columns B and C. |
#4
|
|||
|
|||
Excel Sort Doesn't Work
Peter,
Excel is simply recalcing the formulas _after_ the sort and getting the same values. Copy the cells then paste special values before the sort. HTH, Bernie MS Excel MVP "PeterOut" wrote in message ... On Aug 22, 3:06 pm, Gord Dibben gorddibbATshawDOTca wrote: Good chance that Excel sees the "numbers" as text, especially if copied from somewhere else. Select the numbers and DataText to ColumnsFinish Try the sort again. Gord Dibben MS Excel MVP On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut wrote: I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP 5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. Thank you for your reply. Unfortunately that did not work. I should have mentioned that the data was in column I. I obtained the first element with =$C2-$B2 and dragged the cursor down to get the remaining elements in the column from the values in columns B and C. |
#5
|
|||
|
|||
Excel Sort Doesn't Work
On Aug 22, 4:48 pm, "Bernie Deitrick" deitbe @ consumer dot org
wrote: Peter, Excel is simply recalcing the formulas _after_ the sort and getting the same values. Copy the cells then paste special values before the sort. HTH, Bernie MS Excel MVP "PeterOut" wrote in message ... On Aug 22, 3:06 pm, Gord Dibben gorddibbATshawDOTca wrote: Good chance that Excel sees the "numbers" as text, especially if copied from somewhere else. Select the numbers and DataText to ColumnsFinish Try the sort again. Gord Dibben MS Excel MVP On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut wrote: I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP 5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. Thank you for your reply. Unfortunately that did not work. I should have mentioned that the data was in column I. I obtained the first element with =$C2-$B2 and dragged the cursor down to get the remaining elements in the column from the values in columns B and C. Didn't work for some reason. What option was I supposed to use for paste special? I managed to get it to sort by saving the page as a text file and then reloading it so the numbers were just numbers rather than derivations. I have managed to do it with derivations on another PC so I don't know why it is not working here. Thanks, Peter. |
#6
|
|||
|
|||
Excel Sort Doesn't Work
Peter,
Select the cells that you want to sort, right-click and choose "Copy", then right-click the same selected cells and choose "Paste Special..." - click the button next to "Values" then press OK. Then - do your sort. HTH, Bernie MS Excel MVP Didn't work for some reason. What option was I supposed to use for paste special? |
#7
|
|||
|
|||
Excel Sort Doesn't Work
On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut
wrote: 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 Works fine on mine. Perhaps even though you changed the cell formatting to be proper, the originally entered data is still text. Cut and paste the list into notepad, and then copy and paste it back into excel from there. That will strip any formatting, and paste only numeric data into a numeric cell. |
#8
|
|||
|
|||
Excel Sort Doesn't Work
On Sat, 22 Aug 2009 14:36:21 -0700 (PDT), PeterOut
wrote: On Aug 22, 4:48 pm, "Bernie Deitrick" deitbe @ consumer dot org wrote: Peter, Excel is simply recalcing the formulas _after_ the sort and getting the same values. Copy the cells then paste special values before the sort. HTH, Bernie MS Excel MVP "PeterOut" wrote in message ... On Aug 22, 3:06 pm, Gord Dibben gorddibbATshawDOTca wrote: Good chance that Excel sees the "numbers" as text, especially if copied from somewhere else. Select the numbers and DataText to ColumnsFinish Try the sort again. Gord Dibben MS Excel MVP On Sat, 22 Aug 2009 11:29:36 -0700 (PDT), PeterOut wrote: I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP 5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3). I have the following list of numbers. C-B 0.044991 0.012016 0.027636 0.187865 0.01452 -0.104374 I try to sort them by highlighting them and choosing Data:Sort Sort by C-B. Header row, TRUE. The numbers remain unchanged. What gives? Many thanks in advance, Peter. Thank you for your reply. Unfortunately that did not work. I should have mentioned that the data was in column I. I obtained the first element with =$C2-$B2 and dragged the cursor down to get the remaining elements in the column from the values in columns B and C. Didn't work for some reason. What option was I supposed to use for paste special? Values. I managed to get it to sort by saving the page as a text file and then reloading it so the numbers were just numbers rather than derivations. I have managed to do it with derivations on another PC so I don't know why it is not working here. Because of the way they were originally entered from where you pasted them, despite the cell formatting. Hand enter them again, and that should change. |
Thread Tools | |
Display Modes | |
|
|