Sorting question

  • Thread starter Thread starter JoAnn Paules [MVP]
  • Start date Start date
J

JoAnn Paules [MVP]

Excel 2003

I am working on a spreadsheet at work and would like to be able to change
the sorting order easily. There are two columns I'd like to switch around.

Column 1 is a list of part designators. They can begin with 1W, 2W, or 3W
and are followed by up to a 3 digit number. When I try to sort now, it puts
1W10 before 1W2. I *cannot* change the designator to 1W002. Is there a way
to format that column so that it will yield 1W2 before 1W10 (followed by
2Wnnn and 3Wnnn).

Column 2 are part numbers that are based on part drawing numbers. The
drawing numbers are generally an 8-digit number, however (you saw this one
coming, didn't you?), some of the part numbers are followed with a dash and
a number. For example, I may have part numbers 12345678, 12345679, 12234567,
12234567-1. I would like to be able to sort this column so that it looked at
all of the characters as a group. Right now it puts any number with a dash
at the end of the column.

For now I have it sorted manually but it's a pain in the butt because
sometimes I have a part number and sometimes I have a designator number.
Yes, I know I can use the "Find" feature but I like sorting. I actually have
two worksheets going so if I change one, I need to change the other.

Is there help for me or is this one of those things that I need to learn to
live with?
 
Hi JoAnn,
Excel 2003

I am working on a spreadsheet at work and would like to be able to change
the sorting order easily. There are two columns I'd like to switch around.

Column 1 is a list of part designators. They can begin with 1W, 2W, or 3W
and are followed by up to a 3 digit number. When I try to sort now, it
puts 1W10 before 1W2. I *cannot* change the designator to 1W002. Is there
a way to format that column so that it will yield 1W2 before 1W10
(followed by 2Wnnn and 3Wnnn).
Click to expand...

No. Sort is alphanumeric. Best you can do is;
Copy column 1,
Do Data --> Text to Columns using 'W' as a delimiter to split the data into
two numeric columns,
Sort on both the two new columns.
Column 2 are part numbers that are based on part drawing numbers. The
drawing numbers are generally an 8-digit number, however (you saw this one
coming, didn't you?), some of the part numbers are followed with a dash
and a number. For example, I may have part numbers 12345678, 12345679,
12234567, 12234567-1. I would like to be able to sort this column so that
it looked at all of the characters as a group. Right now it puts any
number with a dash at the end of the column.
Click to expand...

This problem occurs because Excel stores some part numbers as Numeric and
some as text. Change the whole column to text and you should be ok.

To change the whole column to text, select the whole column, then use
Data --> text to columns, clear all delimiters, in step 3 choose Column data
format - Text.
For now I have it sorted manually but it's a pain in the butt because
sometimes I have a part number and sometimes I have a designator number.
Yes, I know I can use the "Find" feature but I like sorting. I actually
have two worksheets going so if I change one, I need to change the other.

Is there help for me or is this one of those things that I need to learn
to live with?
Click to expand...

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com
 
(Please forgive the noobie questions. I'm much better with Publisher than I
am with Excel.)

The delimiter is the dash and I need that. If I get rid if the dash, then
12345678-1 becomes 123456781, doesn't it?

I fear I may be in over my head with this idea.

--

JoAnn Paules
MVP Microsoft [Publisher]
 
Can you use some helper columns (discard or hide them when you're done)???

If yes, then if column 1 is just the 1W followed by the numbers and nothing
else:

=left(a1,2)&text(mid(a1,3,3),"000")
and drag down.

Then sort the data by that helper column.

For the second column, I'd do the same kind of thing. I think your data just
sorts numbers before text. If you had preformatted the column as text, then
done the data entry, I think your data would sort the way you want.

You could convert your data to text by using a helper column:
=b1&""
(numbers will be converted to text and text won't change.)

or if you have leading 0's that are there because of formatting:
=text(b1,"00000000")
drag down and sort by this helper column.

Be aware that just changing the format of a cell is not enough to change the
value (to text). You have to do more (Hitting F2, then enter would be enough
for one cell--after the format was changed to text.)

And one more warning.

When you sort data that contains hyphens, you may be surprised that excel
ignores them in the sort sequence.

From xl2003's help for "Troubleshoot sorting"

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text "A100," Excel
places the cell after a cell that contains the entry "A1" and before a cell that
contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
_ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.
 
i would do as Dave said,and leave tose two helper columns in place with some
sort of fomulas that break the part number into parts and always sort by
those columns so any numbers that are added or appended are put into the
correct place.Hide them or have them over to the side so you dont ussually
see them
--
paul
(e-mail address removed)
remove nospam for email addy!



Dave Peterson said:
Can you use some helper columns (discard or hide them when you're done)???

If yes, then if column 1 is just the 1W followed by the numbers and nothing
else:

=left(a1,2)&text(mid(a1,3,3),"000")
and drag down.

Then sort the data by that helper column.

For the second column, I'd do the same kind of thing. I think your data just
sorts numbers before text. If you had preformatted the column as text, then
done the data entry, I think your data would sort the way you want.

You could convert your data to text by using a helper column:
=b1&""
(numbers will be converted to text and text won't change.)

or if you have leading 0's that are there because of formatting:
=text(b1,"00000000")
drag down and sort by this helper column.

Be aware that just changing the format of a cell is not enough to change the
value (to text). You have to do more (Hitting F2, then enter would be enough
for one cell--after the format was changed to text.)

And one more warning.

When you sort data that contains hyphens, you may be surprised that excel
ignores them in the sort sequence.

From xl2003's help for "Troubleshoot sorting"

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text "A100," Excel
places the cell after a cell that contains the entry "A1" and before a cell that
contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
_ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.


JoAnn Paules said:
Excel 2003

I am working on a spreadsheet at work and would like to be able to change
the sorting order easily. There are two columns I'd like to switch around.

Column 1 is a list of part designators. They can begin with 1W, 2W, or 3W
and are followed by up to a 3 digit number. When I try to sort now, it puts
1W10 before 1W2. I *cannot* change the designator to 1W002. Is there a way
to format that column so that it will yield 1W2 before 1W10 (followed by
2Wnnn and 3Wnnn).

Column 2 are part numbers that are based on part drawing numbers. The
drawing numbers are generally an 8-digit number, however (you saw this one
coming, didn't you?), some of the part numbers are followed with a dash and
a number. For example, I may have part numbers 12345678, 12345679, 12234567,
12234567-1. I would like to be able to sort this column so that it looked at
all of the characters as a group. Right now it puts any number with a dash
at the end of the column.

For now I have it sorted manually but it's a pain in the butt because
sometimes I have a part number and sometimes I have a designator number.
Yes, I know I can use the "Find" feature but I like sorting. I actually have
two worksheets going so if I change one, I need to change the other.

Is there help for me or is this one of those things that I need to learn to
live with?
Click to expand...
Click to expand...
 
If I was going to use a helper column, it was just be two columns with 1
thru whatever. One for designators, one for part numbers. I was hoping there
was some way to do this without adding columns. (The guys don't use Excel
for anything except a table and since this is the end of my first week on
the job, I'd rather not confuse them.)

--

JoAnn Paules
MVP Microsoft [Publisher]




paul said:
i would do as Dave said,and leave tose two helper columns in place with
some
sort of fomulas that break the part number into parts and always sort by
those columns so any numbers that are added or appended are put into the
correct place.Hide them or have them over to the side so you dont ussually
see them
--
paul
(e-mail address removed)
remove nospam for email addy!



Dave Peterson said:
Can you use some helper columns (discard or hide them when you're
done)???

If yes, then if column 1 is just the 1W followed by the numbers and
nothing
else:

=left(a1,2)&text(mid(a1,3,3),"000")
and drag down.

Then sort the data by that helper column.

For the second column, I'd do the same kind of thing. I think your data
just
sorts numbers before text. If you had preformatted the column as text,
then
done the data entry, I think your data would sort the way you want.

You could convert your data to text by using a helper column:
=b1&""
(numbers will be converted to text and text won't change.)

or if you have leading 0's that are there because of formatting:
=text(b1,"00000000")
drag down and sort by this helper column.

Be aware that just changing the format of a cell is not enough to change
the
value (to text). You have to do more (Hitting F2, then enter would be
enough
for one cell--after the format was changed to text.)

And one more warning.

When you sort data that contains hyphens, you may be surprised that excel
ignores them in the sort sequence.

From xl2003's help for "Troubleshoot sorting"

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to
right,
character by character. For example, if a cell contains the text "A100,"
Excel
places the cell after a cell that contains the entry "A1" and before a
cell that
contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
_ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two
text
strings are the same except for a hyphen, the text with the hyphen is
sorted
last.


JoAnn Paules said:
Excel 2003

I am working on a spreadsheet at work and would like to be able to
change
the sorting order easily. There are two columns I'd like to switch
around.

Column 1 is a list of part designators. They can begin with 1W, 2W, or
3W
and are followed by up to a 3 digit number. When I try to sort now, it
puts
1W10 before 1W2. I *cannot* change the designator to 1W002. Is there a
way
to format that column so that it will yield 1W2 before 1W10 (followed
by
2Wnnn and 3Wnnn).

Column 2 are part numbers that are based on part drawing numbers. The
drawing numbers are generally an 8-digit number, however (you saw this
one
coming, didn't you?), some of the part numbers are followed with a dash
and
a number. For example, I may have part numbers 12345678, 12345679,
12234567,
12234567-1. I would like to be able to sort this column so that it
looked at
all of the characters as a group. Right now it puts any number with a
dash
at the end of the column.

For now I have it sorted manually but it's a pain in the butt because
sometimes I have a part number and sometimes I have a designator
number.
Yes, I know I can use the "Find" feature but I like sorting. I actually
have
two worksheets going so if I change one, I need to change the other.

Is there help for me or is this one of those things that I need to
learn to
live with?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top