SORT

[peyman]

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

 

[Gord Dibben]

Some of the numbers are text so you can't format>cells>numbers

Your example input data sorts into your example output data in my Excel 2003

What results are you getting?


Gord Dibben MS Excel MVP

 

[peyman]

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]

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

 

[peyman]

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]

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 Data>Text to Columns>Delimted by>Other type a dash and
Next>Select right hand column and Column Data Format>Text and Finish.

Now sort both columns together.

In C1 enter =IF(B1<>"",A1&"-"&B1,A1) and copy down

Copy column C and Paste Special>Values onto Column A

Delete column B

Hopefully someone will come up with a few less steps.


Gord

 

[peyman]

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.

 

[Pete_UK]

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

 

[peyman]

Thanx Pete and Gord,
I made it!!
Peiman

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

 

[Pete_UK]

You're welcome - glad to hear that.

Pete

 

[Gord Dibben]

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

 

[peyman]

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]

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