Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort and filter
Hello,
I have a coworker who did a filter on her spreadsheet (so the blanks were not visable) and then did a sort ascending by the sequence number. What she got was a sort that was like this: 1, 10,100,1000, 491, 3, and so on. What the heck is going on?!? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort and filter
I suspect those "numbers" are actually text values, so they are being sorted
in a different way than based on the numerical value, rather like b, ba, baa, baaa, ejb, d (though I don't know why your "3" comes after "491". You could insert leading zeroes in front of the number (still as text), or convert the values to actual numbers, if you want a numeric sort. Hope this helps. Pete "JoAnn" wrote in message ... Hello, I have a coworker who did a filter on her spreadsheet (so the blanks were not visable) and then did a sort ascending by the sequence number. What she got was a sort that was like this: 1, 10,100,1000, 491, 3, and so on. What the heck is going on?!? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort and filter
That is because your numbers are defined as texts, so it sorts them
"alphabetically". A trick to convert "text numbers" to numbers: 1. Write in some cell the value 1. 2. Copy that cell (press on it Ctrl+C). 3. Select all your numbers that are mistakenly considered as texts. 4. Go into "Paste special" (if it is in older versions of excel it is through the "Edit" menu. In Excel 2007 it is through the small arrow under the "Paste" button). 4. Select the "Multiply", and click "OK". 5. Thats it! All the numbers, are now converted to become real numbers. Good luck! http://www.free-training-tutorial.com "JoAnn" wrote: Hello, I have a coworker who did a filter on her spreadsheet (so the blanks were not visable) and then did a sort ascending by the sequence number. What she got was a sort that was like this: 1, 10,100,1000, 491, 3, and so on. What the heck is going on?!? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort and filter
Hi,
It looks to me as if your co-worker has numbers and text that look like numbers in this column. Put a 1 in a spare cell and copy it. Remove the filter and select the entire data range and then Edit|Paste Special - Select Multiply and click OK Delete the 1 you put in the cell, apply the filter and have another go at the sort. Mike "JoAnn" wrote: Hello, I have a coworker who did a filter on her spreadsheet (so the blanks were not visable) and then did a sort ascending by the sequence number. What she got was a sort that was like this: 1, 10,100,1000, 491, 3, and so on. What the heck is going on?!? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter & Sort | Excel Worksheet Functions | |||
how do I sort and filter by different criteria | New Users to Excel | |||
Sort & Filter | Excel Discussion (Misc queries) | |||
Filter, sort and sum | Excel Discussion (Misc queries) | |||
Filter and sort | Excel Discussion (Misc queries) |