Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
Hi,
I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
Some of the numbers are text so you can't formatcellsnumbers
Your example input data sorts into your example output data in my Excel 2003 What results are you getting? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 15:27:01 -0700, peyman wrote: Hi, I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
Thanx Gord,
I'm also using Excel2003 but when I sort it, first the numbers line up and then the numbers with dash numbers and at the end the part numbers starting with letters like below: 103528 205999 205999-011 205999-051 pl705280 pl705283 Any suggestions? Peiman "Gord Dibben" wrote: Some of the numbers are text so you can't formatcellsnumbers Your example input data sorts into your example output data in my Excel 2003 What results are you getting? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 15:27:01 -0700, peyman wrote: Hi, I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
I am confused.
The sort order example you just posted is the same as the sort order you requested in the "like below" example in your original post. Post an example of what you think the order should be, not what you are currently getting. Gord On Sat, 19 Jul 2008 08:46:09 -0700, peyman wrote: Thanx Gord, I'm also using Excel2003 but when I sort it, first the numbers line up and then the numbers with dash numbers and at the end the part numbers starting with letters like below: 103528 205999 205999-011 205999-051 pl705280 pl705283 Any suggestions? Peiman "Gord Dibben" wrote: Some of the numbers are text so you can't formatcellsnumbers Your example input data sorts into your example output data in my Excel 2003 What results are you getting? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 15:27:01 -0700, peyman wrote: Hi, I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
Sorry Gord,
Assume there are more Part numbers.I'm adding them to the list to show you where the problem is: 100321 103528 205999 100321-001 103528-011 205999-011 205999-051 pl705280 pl705283 I get the above list when the list is sorted but the desired sort should be like below: 100321 100321-001 103528 103528-011 205999 205999-011 205999-051 pl705280 pl705283 Thanx "Gord Dibben" wrote: I am confused. The sort order example you just posted is the same as the sort order you requested in the "like below" example in your original post. Post an example of what you think the order should be, not what you are currently getting. Gord On Sat, 19 Jul 2008 08:46:09 -0700, peyman wrote: Thanx Gord, I'm also using Excel2003 but when I sort it, first the numbers line up and then the numbers with dash numbers and at the end the part numbers starting with letters like below: 103528 205999 205999-011 205999-051 pl705280 pl705283 Any suggestions? Peiman "Gord Dibben" wrote: Some of the numbers are text so you can't formatcellsnumbers Your example input data sorts into your example output data in my Excel 2003 What results are you getting? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 15:27:01 -0700, peyman wrote: Hi, I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SORT
Aha!
Larger example<g Excel sorts numbers before text. The numbers with the dashes are text. Your first example was just a coincedence that it sorted properly. Assuming data is in A1:Axx Some work to do but DataText to ColumnsDelimted byOther type a dash and NextSelect right hand column and Column Data FormatText and Finish. Now sort both columns together. In C1 enter =IF(B1<"",A1&"-"&B1,A1) and copy down Copy column C and Paste SpecialValues onto Column A Delete column B Hopefully someone will come up with a few less steps. Gord On Sat, 19 Jul 2008 09:59:00 -0700, peyman wrote: Sorry Gord, Assume there are more Part numbers.I'm adding them to the list to show you where the problem is: 100321 103528 205999 100321-001 103528-011 205999-011 205999-051 pl705280 pl705283 I get the above list when the list is sorted but the desired sort should be like below: 100321 100321-001 103528 103528-011 205999 205999-011 205999-051 pl705280 pl705283 Thanx "Gord Dibben" wrote: I am confused. The sort order example you just posted is the same as the sort order you requested in the "like below" example in your original post. Post an example of what you think the order should be, not what you are currently getting. Gord On Sat, 19 Jul 2008 08:46:09 -0700, peyman wrote: Thanx Gord, I'm also using Excel2003 but when I sort it, first the numbers line up and then the numbers with dash numbers and at the end the part numbers starting with letters like below: 103528 205999 205999-011 205999-051 pl705280 pl705283 Any suggestions? Peiman "Gord Dibben" wrote: Some of the numbers are text so you can't formatcellsnumbers Your example input data sorts into your example output data in my Excel 2003 What results are you getting? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 15:27:01 -0700, peyman wrote: Hi, I've got a list of part numbers like: 205999 pl705283 pl705280 205999-011 103528 205999-051 How can I sort this column to have the part nos. like below?Should I change the "format cells/numbers" for the cells of this column: sorted coulmn: 103528 205999 205999-011 205999-051 pl705280 pl705283 Thanx for ur help. Peiman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
Ascending Sort formula, change to neg #: descending sort.. | Excel Discussion (Misc queries) | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |