Default Sort Order

I am trying to find a means to accomplish a true 
alpha/numeric sort. The current default sort orders 
places numerics before alphas but I need to sort alphas 
before numerics. This is being used to sort a parts list 
which has mixed characters throughout. Any assistance in 
this endeavor would be greatly appreciated.

Ken Wilkes
0
kwilkes (1)
10/27/2003 6:41:19 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
308 Views

Similar Articles

[PageSpeed] 38

If you are interested only in changing how the first character is interpreted/sorted, you could
use a helper column with a formula like =IF(LEFT(A1,1)>="A",0,1)  Then sort with that column as
the 1st key and the part number as the 2nd key.

But if you want to deal with embedded letters, to, i.e. to have 1A123 sort ahead of 11123, the
above will not work. There is no way to change the built-in sort. First, you will have to have
all of the part numbers entered as text, even those that consist only of digits.

The you will probably need to develop a macro. I envision a UDF which creates a new string in
which the numbers are translated to characters which come after "Z". That new string would be
placed in a separate column which would be used as the key for the sort.

On Mon, 27 Oct 2003 10:41:19 -0800, "Ken Wilkes" <kwilkes@pulau.com> wrote:

>I am trying to find a means to accomplish a true 
>alpha/numeric sort. The current default sort orders 
>places numerics before alphas but I need to sort alphas 
>before numerics. This is being used to sort a parts list 
>which has mixed characters throughout. Any assistance in 
>this endeavor would be greatly appreciated.
>
>Ken Wilkes

0
myrnailarson (145)
10/27/2003 8:07:56 PM
With your existing partnumbers in Column E  -  Temporarily create a new
column  F
and enter in F2 (say in this example):

=IF(AND(CODE(LEFT(E2,1))>=49,CODE(LEFT(E2,1))<=57),"zz"&E2,E2)

and Copy down.    This will Append a "zz" at the beggining of all part
numbers that start with a numeric.  Copy and Paste-Special Values of
Completed Column F,

Sort as usual -- All numeric part numbers should be at the bottom preceeded
with "zz".
Now let's fix them back --  in column G (temp column) enter:

=MID(G6,3,LEN(G6)-2)   <<  where G6 is your first ZZ123  - Copy down

On G Column  Copy and Paste-Special Values.
Move corrected G Col Numerics over to Col F  <<  Which is your data sorted
as you want it.

Hum,,,,
Is this too much, or not  LOL

"Ken Wilkes" <kwilkes@pulau.com> wrote in message
news:095f01c39cb9$e92a1d40$a001280a@phx.gbl...
> I am trying to find a means to accomplish a true
> alpha/numeric sort. The current default sort orders
> places numerics before alphas but I need to sort alphas
> before numerics. This is being used to sort a parts list
> which has mixed characters throughout. Any assistance in
> this endeavor would be greatly appreciated.
>
> Ken Wilkes


0
jmay (696)
10/31/2003 10:03:21 AM
Reply:

Similar Artilces:

New items with purchase order integration
I'm running a daily integration to bring purchase orders into GP using Integration Manager. The source is a text file. It works very well. The problem I have is with new items. When there is an item in the source file that does not exist in Great Plains, I would like the integration to fail or skip the line item. Currently, it brings the item into the Purchase Order as a non inventory item. The document will show the item number and the cost but no item description. Where can I change the behavior for this? Unfortunately, with the users that put non-inventory items on POs Wh...

Default mailbox rights #2
Hello, Is there an MS white paper that shows the specific default special permissions found in AD Users and Computers - Exchange Advanced - Mailbox Rights? Thank you, JVM Try these two articles: http://support.microsoft.com/kb/328229/en-us http://www.microsoft.com/technet/prodtechnol/exchange/guides/E2k3ADPerm/cc98c853-7bd2-47bc-b88f-36163f1306f1.mspx Nue "J Meyer" <JMeyer@discussions.microsoft.com> wrote in message news:890E6D32-C501-45E2-BD57-6E036DD8D3CC@microsoft.com... > Hello, > > Is there an MS white paper that shows the specific default special > p...

Sort Order on Order Fulfillment Window
In the sales order fulfillment window you can sort the lines items by Order Entered or Site/Bin/Item. Does the Site/Bin/Item only sort by Bin if Multi-bin is used? I am not using multi-bin but have created bin for each item in each site on the Quantities/Sites window. thanks. ...

Open default mail client programmatically
Hi all, Is there a function in visual c++ to open default mail client and pass to it destination addr, subject, body an an attachement (like SendObject in VB). Thanks. ShellExecute mailto:\\ -- - Mark Randall http://zetech.swehli.com "MB2" <m.b2@laposte.net> wrote in message news:d9lqfe$ors$1@apollon.grec.isp.9tel.net... > Hi all, > Is there a function in visual c++ to open default mail client > and pass to it destination addr, subject, body an an attachement (like > SendObject in VB). > > Thanks. > > >Is there a function in visual c++ to o...

Determining the apex of a order 2 polynomial
History: I created a spreadsheet with functions only that will provide feedback from a data point on an xy scatter chart. I charted several thousand cells with simple functions such that =if(and(a,b),1,0) to provide feedback based on these coordinates and then used v or h lookup to come up with the adjusted information. This is for a field determination of what the data points should produce. I thought it would be nice to duplicate the form for a similar process in the lab. We frequently test the field information to gain more exact information in the lab. This would allow me to show...

Sort House Numbers
How can I get excel to sort house numbers as I would like, i.e 1,2,2a,3,4,5,5a,5b, et. etc. So far I've ailed miserably. Any help out there? TIA Mike G Hi Mike, You will have to separate the digits into a separate cell. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Extract the First Set of Digits (#DigitsFirstID) http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

change order in which gwes loads display, touch, hid drivers
I cannot figure out how to do this, but I need GWES to load the display driver FIRST (for a splash screen implementation). Cannot find any registry values that accomplish this... any ideas? Come on guys, somebody has to know the order in which driver's are loaded by GWES.exe and how to alter it.... MS gentlemen???? I don't think that's going to help. What you really want to do is load the image in the bootloader, since, by the time GWES itself loads, you're probably 75% of the way through the boot process. As far as I can recall, GWES loads the keyboard, mouse, a...

Change Order date?
I have situation where we had to enter some orders into CRM like 3 weeks after the fact but there was no way to to back date them when entering so that they fall into the proper months reports, etc. Any help out there on how I can change the dates at the database level? Look in the SalesOrderBase table. Mike "SteveT" <drumguy61-google@yahoo.com> wrote in message news:4635de59.0402120638.1bd39e41@posting.google.com... > I have situation where we had to enter some orders into CRM like 3 > weeks after the fact but there was no way to to back date them when > entering ...

Why doesn't the page preview have the same order as work page?
Why doesn't the preveiw page look like the one I built on the work pages. I have "saved as" and "saved. The nagagation bars in preview goes to different pages than titled. HELP!!!!!! ...

Pivot Table Defaults Turn Off Subtotal
Is there a way to change the default behavior of Pivot Tables? Specifically, can I change the default behavior for a Field to NOT show the subtotals? I know how to turn it off easy enough - it's just frustrating doing it over and over. Thoughts? I have not answer, just want to agree that this is a frustrating issue. Ie, everytime I build a Pivot Table, I have to go through the process of manually turning off the subtotals. Another issue I run into is that generally my pivot tables need to have the 'Field Settings' / 'Layout & Print' changed via a manual process ...

Sorting Stacked Columns
I am trying to sort the columns so that the largest value is on th bottom each year. This would change the order of the series over eac year presumably. I have not figured out a way to do this, and fear i may be VBA related. Any ideas? Thanks. I attached the file for viewing Attachment filename: testbook_stackedcols.xls Download attachment: http://www.excelforum.com/attachment.php?postid=61644 -- Message posted from http://www.ExcelForum.com ...

Start a file at a defaulted worksheet/cell?
Howdy, Having a few users to a file, can I have the file open to a default worksheet/cell as I have directions I want them to read before playing... Regards, Kevin Kevin, use something like this, put in thisworkbook code Private Sub Workbook_Open() Sheets("Sheet2").Select Range("C3").Select End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "...

Insert Blank Line and Sort
------------liX6Lxsnq7Ovs6LAASpU5y Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Within a worksheet I have an example of data below consisting of four columns. I need to put a blank line in between the Racecourse (where they change) and after do a sort on Rating (high to low). Is it possible? I could do it with a macro, but am unable to fathom out how to deal with a differing number of horses at each racecourse. Time Racecourse Horse Rating 04:30:00 SANDOWN Dare To Dance 0.261 04:30:00 SANDOWN Garud 0.153 04:30:00 SANDOWN Wes...

Making custom form default for contacts
Hello: I am a beginner with Outlook. I am a new hire in a department that uses Outlook contact information in a Word Mail Merge process. I am teaching myself how this all works, and have been doing ok, but I am struggling with forms, particularly the form used for contacts. I don't have any problems modifying the form (for example to add FirstName and LastName fields), but I am very confused about how to get Outlook to use my modified form instead of the default custom contacts form. It seems to me that this should be a simple property setting, but I've had no luck tinkering w...

Large List Sorting
Hello, All! Have a problem. I'm developing an application which reading a large database file (DBF) and displaying it in the list control. Because of the size of the file (>15 MB, later it will be increased) I'm using a virtual list (owner data). But now I need to sort data in the list. AFAIK to perfirm this I should read the whole file. In this case my program starts several minutes!!! It doesn't have to be like this [(C) Pink Floyd]... Is there any methods to load a large amount of data as quickly as possible? Or I just should humble, 'cause sorting of large amount of d...

Sorting Issue (Mixde Fields)
I have an issue where I need to sort a worksheet based on a field (Bin Location)that is sorting as seen below... 1A1 1A20 1A21 1A22 1A3 1A4 How can I get Excel to sort it the following way... 1A1 1A3 1A4 1A20 1A21 1A22 Please help.... I need to get this figured out today. I appreciate any responses. Thanks, Jonathan G. One way: add a column showing the length of the column you want to sort, then sort using the new column then the "real" column. "Jonathan G." wrote: > I have an issue where I need to sort a worksheet based on a field (Bin > Location)that is s...

Default Signatures using Outlook Web Access
Because I primarily access email via Outlook Web Access (do not use desktop version of Outlook), I would like to have new/reply messages in OWA default to use a standard signature. I know how to set this up with the desktop version of Outlook. I have also seen how a signature can be picked if your desktop version of Outlook is "configured and runnable" when you are using OWA. However, can a standard signature be set up when only using OWA? Thanks, I would appreciate any help. What version of Exchange are you using? Exchange 2003 OWA offers a default signature. There are a...

Changing default administrator account in CRM3.0
Hi, I want to know how to change the default administrator of the crm server Thanks in advance. -- Regards, Bishwarup Create a new user profile and assign that user the "System Administrator" role. Then log in to CRM as that user and revoke the "System Administrator" role from your current administrator. Dave "Bishwarup" <Bishwarup@discussions.microsoft.com> wrote in message news:D0C818F1-EDA4-4B7B-A254-A286CE3F9E1B@microsoft.com... > Hi, > > I want to know how to change the default administrator of the crm server > > Thanks in adv...

How to sort in Excel with headers link to all body parts
I have a table that have subheaders and each subheader with rows of items below them. I wanted to sort all subitems from all subheaders according to value (in one column); and have each subitem link to the subheader as label for that particular item. I guess what I need to do is link a cluster of items to it subheader; so when I sort each item will include the subheader as label to show what subheader it came from. ...

View pareto chart by sorting the max number on the bottom
Is it possible to format the pareto chart by sorting the maximun number show in the bottom of each bar and ignore sort by Legend item? The order is determined by the Row Source of the chart. -- Duane Hookom Microsoft Access MVP "sq75222" wrote: > Is it possible to format the pareto chart by sorting the maximun number show > in the bottom of each bar and ignore sort by Legend item? > > ...

criteria default value?
I've created a query that has a from/to criteria as shown below: >=[From Pre-School Number :] And <=[To Pre-School Number :] The possible numeric range is 1-99, so entering 7 and 7 gives you just Pre-School 7's details, or entering 1 and 99 gives you every Pre-School's details. Is there some way to have the "Enter Parameter Value" boxes come up with a default value already in there (say 1 and 99 respectively), which the user can then typeover if they choose. Thanking you in anticipation. You can create an Access Form [frmPreQuery] with 2 Textboxes (defaulted ...

Purchase order Processing
Hi, A purchase order that was received, not posted, got posted in error. What is the best way of "reversing" this error? -- P McCarthy Hi-- You would enter a return, transactions>>purchasing>>returns trx entry. If it was only received, you would choose to enter Return type. If it was received and invoiced, you would enter a Return with Credit. Please note, this will NOT open the PO back up to be received, you will have to enter a new PO or if the PO is not completely received, you could also edit it to increase the amountt to be received. Take care, Christina N...

Sorting Notes
I use the Outlook Notes program quite a bit. But, there are two problems I have with it: 1. It sorts most recent to oldest -- is there any way to change the sort order to alphabetical? 2. I'd like the default "note" size to be a little larger? Is this possible? Thanks I can answer part of this one. To make the note a little larger - Tools, Options, Note Options, Size. Judy Freed <EHPorter> wrote in message news:414a4d0d$0$55835$a1866201@newsreader.visi.com... > I use the Outlook Notes program quite a bit. But, there are two problems I > have wit...

Default Account #2
Hi, I have tried to setup my 2 email addresses in Outlook 2003. I have a 'colin@mydomain.com' account and a 'ceo@mydomain' account. I want to be able to send from both accounts but only receive on the 'ceo' account. This works fine but, as the 'ceo' account is set as default, whenever someone receives an email from me, the 'reply to' field shows the other account and also, the header states 'sent by ceo@mydomain.com on behalf of colin@mydomain.com'. Does anyone know a way round this so that the email appears to come from whichever account...

Excel default formatting of numbers
I have a CSV file which is generated by an inhouse application. One of the columns in this file has a four digit code, which are text but made of numerical digits. Some of the codes start with one or more zeroes, ie 0012, 0013, 0014 etc. The problem is when my users open the file in Excel the column has been formatted as numbers and the leading zeroes have been trimmed, so 0013 becomes 13. I do not want my users to have to play around with formatting. Also even if I do select the column and change the formatting to text the leading zeroes are still missing. Please could anyone help me with a ...