| 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
Hi Gord,
Thank you so much for your reply but it didn't work for me completey.the result is when I sort both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: .... 938333 011 938333 013 938333 .... Any solution to bring up the 938333 before the two above to have : .... 938333 938333 011 938333 013 .... Thanx again. "Gord Dibben" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
An empty cell sorts at the end - apply autofilter to your second
column, select blanks from the filter pull-down, select the first cell displayed and type a single <space in there, then copy that space down the rest of the blank cells. The select All from the filter pull- down, and then try your sort on both columns again. Hope this helps. Pete On Jul 21, 6:53*pm, peyman wrote: Hi Gord, Thank you so much for your reply but it didn't *work for me completey.the result is when I sort *both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: ... 938333 *011 938333 *013 938333 ... Any solution to bring up the 938333 before the two above to have : ... 938333 938333 *011 938333 *013 ... Thanx again. "Gord Dibben" wrote: 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- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
Thanx Pete and Gord,
I made it!! Peiman "Pete_UK" wrote: An empty cell sorts at the end - apply autofilter to your second column, select blanks from the filter pull-down, select the first cell displayed and type a single <space in there, then copy that space down the rest of the blank cells. The select All from the filter pull- down, and then try your sort on both columns again. Hope this helps. Pete On Jul 21, 6:53 pm, peyman wrote: Hi Gord, Thank you so much for your reply but it didn't work for me completey.the result is when I sort both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: ... 938333 011 938333 013 938333 ... Any solution to bring up the 938333 before the two above to have : ... 938333 938333 011 938333 013 ... Thanx again. "Gord Dibben" wrote: 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- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
You're welcome - glad to hear that.
Pete On Jul 21, 8:40*pm, peyman wrote: Thanx Pete and Gord, I made it!! Peiman |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
Each post brings up more wrinkles<g
Assume you have followed the previous steps and Column A is all you have left. In B1 enter =TEXT(A1,"000000") Copy down. Sort on column B and select "sort numbers and numbers stored as text separately" Gord On Mon, 21 Jul 2008 10:53:01 -0700, peyman wrote: Hi Gord, Thank you so much for your reply but it didn't work for me completey.the result is when I sort both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: ... 938333 011 938333 013 938333 ... Any solution to bring up the 938333 before the two above to have : ... 938333 938333 011 938333 013 ... Thanx again. "Gord Dibben" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
Thanx Gord,
You are great. I was wondering if you can explain to me how the formula(=text(A1,"000000") work ?And why 6 zeroes (000000) and not more or less? Thanx again "Gord Dibben" wrote: Each post brings up more wrinkles<g Assume you have followed the previous steps and Column A is all you have left. In B1 enter =TEXT(A1,"000000") Copy down. Sort on column B and select "sort numbers and numbers stored as text separately" Gord On Mon, 21 Jul 2008 10:53:01 -0700, peyman wrote: Hi Gord, Thank you so much for your reply but it didn't work for me completey.the result is when I sort both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: ... 938333 011 938333 013 938333 ... Any solution to bring up the 938333 before the two above to have : ... 938333 938333 011 938333 013 ... Thanx again. "Gord Dibben" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SORT
The formula changes the numbers to text.
The TEXT function requires a formatting argument. You can't just enter TEXT(A1) when A1 contains 123456 You have 6 digits in each part number that doesn't have the -011 suffix If you used more than 6 zeros in the formatting you would get 0xxxxxx or 00xxxxxx Gord On Mon, 21 Jul 2008 15:55:00 -0700, peyman wrote: Thanx Gord, You are great. I was wondering if you can explain to me how the formula(=text(A1,"000000") work ?And why 6 zeroes (000000) and not more or less? Thanx again "Gord Dibben" wrote: Each post brings up more wrinkles<g Assume you have followed the previous steps and Column A is all you have left. In B1 enter =TEXT(A1,"000000") Copy down. Sort on column B and select "sort numbers and numbers stored as text separately" Gord On Mon, 21 Jul 2008 10:53:01 -0700, peyman wrote: Hi Gord, Thank you so much for your reply but it didn't work for me completey.the result is when I sort both the columns as per your advise, the part numbers with dash come before the part number without dash number like below: ... 938333 011 938333 013 938333 ... Any solution to bring up the 938333 before the two above to have : ... 938333 938333 011 938333 013 ... Thanx again. "Gord Dibben" wrote: 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 | |||
Linear Mode
