Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
odd sort issue with numbers
I am trying to sort a column of data on my worksheet that contains a
bunch of large numbers. When I did this programically, I noticed that while it was sorting "classes" of numbers correctly, the overall sort was not correct. What I mean by this is I have a bunch of numbers, some of which begin with 65, others that begin with 23 (plus more types, but this will serve for an example). 23069000 is clearly greater than 65116, but when I sort it in ascending order 23069000 is above 65116 in the list. However, 65115 will be above 65116, and 23069000 will be above 23069001. So I get something like this: 23069000 23069001 65115 65116 (What I want is something like this: 65115 65116 23069000 23069001 i.e. sorted in proper numeric order) I figured something was amiss with my code, so I tried sorting using the data-sort menu, and much to my surprise got the same result, so the problem is not my code, but something I'm not seeing in the sort process. This column is formated as a number (rather than general or text). Here is my code snippet in case it should help: Sub sort() With Worksheets(1) .Range("A:F").sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub (I also tried sorting with just the first range (C2), which has the numbers mentioned above, but it made no difference in the result.) As always, any help is very much appreciated. :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
odd sort issue with numbers
assume one of the troublesome numbers is in cell c1. In another cell put in
=isnumber(c1) My guess is it will return false and your numbers are stored as text (which is the way they are being sorted). -- Regards, Tom Ogilvy "Lilivati" wrote: I am trying to sort a column of data on my worksheet that contains a bunch of large numbers. When I did this programically, I noticed that while it was sorting "classes" of numbers correctly, the overall sort was not correct. What I mean by this is I have a bunch of numbers, some of which begin with 65, others that begin with 23 (plus more types, but this will serve for an example). 23069000 is clearly greater than 65116, but when I sort it in ascending order 23069000 is above 65116 in the list. However, 65115 will be above 65116, and 23069000 will be above 23069001. So I get something like this: 23069000 23069001 65115 65116 (What I want is something like this: 65115 65116 23069000 23069001 i.e. sorted in proper numeric order) I figured something was amiss with my code, so I tried sorting using the data-sort menu, and much to my surprise got the same result, so the problem is not my code, but something I'm not seeing in the sort process. This column is formated as a number (rather than general or text). Here is my code snippet in case it should help: Sub sort() With Worksheets(1) .Range("A:F").sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub (I also tried sorting with just the first range (C2), which has the numbers mentioned above, but it made no difference in the result.) As always, any help is very much appreciated. :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
odd sort issue with numbers
Your numbers are text,
Formatting to number is not enough. Format to General or number then copy an empty cell and select your data. Paste SpecialAddOKEsc Gord Dibben MS Excel MVP On 6 Jul 2006 10:48:18 -0700, "Lilivati" wrote: I am trying to sort a column of data on my worksheet that contains a bunch of large numbers. When I did this programically, I noticed that while it was sorting "classes" of numbers correctly, the overall sort was not correct. What I mean by this is I have a bunch of numbers, some of which begin with 65, others that begin with 23 (plus more types, but this will serve for an example). 23069000 is clearly greater than 65116, but when I sort it in ascending order 23069000 is above 65116 in the list. However, 65115 will be above 65116, and 23069000 will be above 23069001. So I get something like this: 23069000 23069001 65115 65116 (What I want is something like this: 65115 65116 23069000 23069001 i.e. sorted in proper numeric order) I figured something was amiss with my code, so I tried sorting using the data-sort menu, and much to my surprise got the same result, so the problem is not my code, but something I'm not seeing in the sort process. This column is formated as a number (rather than general or text). Here is my code snippet in case it should help: Sub sort() With Worksheets(1) .Range("A:F").sort _ Key1:=Range("C2"), _ Order1:=xlAscending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub (I also tried sorting with just the first range (C2), which has the numbers mentioned above, but it made no difference in the result.) As always, any help is very much appreciated. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Issue | Excel Discussion (Misc queries) | |||
Sort issue | Excel Worksheet Functions | |||
sort issue | Excel Programming | |||
Sort Issue | Excel Programming | |||
Sort issue | Excel Programming |