Sorting in excel
Posted by Janis on July 25, 2001 10:32 AM
I have asked several people and no one seems to have
an answer to this problem:
I have an excel worksheet which I call Customer
Cross Reference. Info is loaded with the job number,
customer, part name. The job number is a chronologically
assigned number. For instance, I have job number
1225, but I also have 1225-1, 1225-2 (which are just
a modified version of the original job number). When
I try to sort this list by customer, then job number
here's what happens. Any number which does not have
a dash no. after it is sorted first. All dash numbers
for any particular customer go to the end of the list.
What I am trying to get is a list sorted in order,
that might look something like this:
ABC Corp. 2122
2123-2
2123-25
2124
2125
2887
2900
XYZ Corp. 1005
1006
1006-1
1006-1A
1006-2
When I do a sort on the complete listing, all dash
numbers for any particular customer go to the end
of that particular customers' job. I would like them
to go in order of job number followed directly by
dash numbers.
I am making this problem clear? If not, let me know.
I would appreciate help on this!
Thanks!
Janis
Posted by Bill on July 25, 2001 10:39 AM
Hi Janis,
I'll bet that your original job number (without the dash) is ending up as a number. The job numbers with the dashes are definitely ending up as text entries.
If you could force all of the original job number entries to be text, then it will sort correctly. There are 2 ways to do this:
1) Remember to put an apostrophe before every job number when you enter it: '1725. The apostrophe won't show up in the cell, but it tells Excel to treat the entry as text.
2) Format the cells in a as Text using Format > Cells > Number. Pick text from the list.
Unfortunately, using trick #2 will not help the already existing entries in column A to text. One quick way would be to insert a temporary column in B, enter a formula in B2 of
=TEXT(A2,"0")
Copy this formula down to all of the numeric entries.
Then, highlight B2:B??, use Edit > Copy, Edit > PasteSpecial > Values to change the formulas to values. You can then copy B2:B?? over top of A2:A??, delete the temporary B. It should now sort correctly.
Bill
Posted by Ian on July 26, 2001 5:20 AM
download the free ASAP add-in
www.asaputilities.nl/
once in stalled highlight your column and use the ASAP_Numbers_Convert numbers to text (adding ' in front)
very good add-in
BUT!! be careful if you do want to use this, they're macro's you can't undo them.
Ian
