Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need Help with Sorting Problems
This originally got buried under someone's February post so please forgive
the reposting... User has Excel 2000. This spreadsheet has a column identified as an ID# but the numbering is not consistent. The majority within the data are 5-digit numbers. There are some that start with a leading zero. There are also some 10+ digit numbers which have letters in them, generally at the end. There are even some records that do not have any ID and that field is blank. So far we have not been able to get a good sort out of the data. The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit numbers, then followed by the 5-digit numbers with a leader zero. Then it gives some of the 10+ digits #s which include a letter within a sort order starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH, etc.). Then some 10xxx numbers start up again and we start numbering all over. The blank cell rows are sorted at the END of the document when I would think they would have been put at the top. I have copied entire sheet to new document and pasted as special values. I have copied just this column of data as well as another column 100% populated with data and pasted special values. I have formatted the column to text and as number. The data continues to sort in the same incorrect fashion. The puzzler is that it does sort 100% correctly in Excel XP. So, can anybody suggest anything to try in Excel 2000 or is the only answer for the user to be upgraded? TIA! |
#2
|
|||
|
|||
Brenda,
Excel sorts numbers to the top, followed by text, followed by blanks. So 9876 (a number) comes before 12345A (text). Excel 2002 has the capability to sort text that looks like numbers as numbers. You should be able to sort the data the way you want by converting all of the data to text and then sorting it. This can be quickly accomplished by selecting the sort column and using... Data | Text to Columns. In the "Wizard" that appears, use the defaults, except for Step 3 where you should check the "Text" button. You should now be able to sort sequentially. Regards, Jim Cone San Francisco, USA "Brenda Rueter" wrote in message ... This originally got buried under someone's February post so please forgive the reposting... User has Excel 2000. This spreadsheet has a column identified as an ID# but the numbering is not consistent. The majority within the data are 5-digit numbers. There are some that start with a leading zero. There are also some 10+ digit numbers which have letters in them, generally at the end. There are even some records that do not have any ID and that field is blank. So far we have not been able to get a good sort out of the data. The sort first gives us the 11xxx numbers, followed by 2 through 8 5-digit numbers, then followed by the 5-digit numbers with a leader zero. Then it gives some of the 10+ digits #s which include a letter within a sort order starting with the first number (e.g., 123485767GH, 135679848B, 348674798HH, etc.). Then some 10xxx numbers start up again and we start numbering all over. The blank cell rows are sorted at the END of the document when I would think they would have been put at the top. I have copied entire sheet to new document and pasted as special values. I have copied just this column of data as well as another column 100% populated with data and pasted special values. I have formatted the column to text and as number. The data continues to sort in the same incorrect fashion. The puzzler is that it does sort 100% correctly in Excel XP. So, can anybody suggest anything to try in Excel 2000 or is the only answer for the user to be upgraded? TIA! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Problems | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) | |||
Sorting Problems | Excel Discussion (Misc queries) | |||
Problems with sorting a pivot table | Excel Discussion (Misc queries) | |||
Sorting Problems | Excel Discussion (Misc queries) |