Sort Parameters

I have a list of different titles, some with words like "the" at the front 
and I cant figure out how or if its possible to sort the list alphabetically 
with out Excel picking up those words as the primary word to sort by.  Is it 
possible to make excel ignore those words when sorting? And if so how?  
DLZ217 (3)
4/25/2005 7:08:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 54

To remove  "The" as the first word in a helper column to be sorted
   A2:   'The Cat and the Cradle
   B2:   =IF(LEFT(UPPER(A1),4) = "THE ",MID(A1,5,200),A1)
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:
Search Page:

"DLZ217" <> wrote in message
> I have a list of different titles, some with words like "the" at the front
> and I cant figure out how or if its possible to sort the list alphabetically
> with out Excel picking up those words as the primary word to sort by.  Is it
> possible to make excel ignore those words when sorting? And if so how?
> Thanks!

dmcritchie (2586)
4/25/2005 10:35:11 AM

Similar Artilces:

Sorting #11
How do you sort a list with more than three sort keys? First sort on least significant key(s) Then sort on most significant keys HTH -- AP "Fran" <> a �crit dans le message de news: %23pS%239BXeGHA.1856@TK2MSFTNGP03.phx.gbl... > How do you sort a list with more than three sort keys? > You need a helper column. In this should be a formula concatenating all the fields to be sorted. =A1&B1&C1 ... This will sort only in ascending order. You will need to include the value of dates (not the actual dates) - &VALUE(A1) or &...

dynamic field content of parameter combobox
Greetings, I have to filter my Crystal 9.0 report with a parameter combobox field in Microsoft CRM. The data for this parameter field has to be loaded out of a database field dynamically. I have found the possibility to load database field values in a parameter field but this will not be dynamically. If there is a change in the database content the parameter field content will not be updated. I hope to create a combobox control is no problem. Is there a way to turn this into reality? Thanks for your help Thomas Ott (ITVT germany) ...

sorting in Excel #6
I inherited an Excel document that, when I go to sort, has the sort choice set to always be "header row" with first sort being "last name" and second sort being "first name." I'd like to set up the sort function in a new document the same way. How do I do that? Thanks. OW Hi AFAIK Excel stores this information after your first sort. So simply set in your new table the sorting according to your requirements. Excel should remember these settings as default -- Regards Frank Kabel Frankfurt, Germany "Oran Walker" <anonymous@discussions.micro...

how to automatically run a report with parameters and email the report based on
Hi all, I have a report which uses a username as an input parameter. I need to schedule the report to run automatically and email only the page of the report which belongs to each user, to that user's email address. The stored procedure I'm using for the report already uses the username as an input parameter. The problem is that I cannot automatically run the report for each of the parameter values separately. Thanks in advance... ...

Sort data by formatting
Is there any way to sort rows of data depending on their background color? Like I have around 1000 mixed up rows of 5 colors (e.g. yellow, red, green, violet, blue), and they are arranged in no specific order i.e first 2 yellow rows, then 1 red, 4 blue, then again 3 yellow, and 3 blue on. I just want to arrange them in block of Colors like All Blue Rows, All Yellow Rows etc. Could it arrange using VBA or Excel? Hi check out for some ideas Cheers JulieD "Murtaza" <NoEmail@NoEmail> wrote in message news:OVD5bWWKFHA.147...

sort by amount
It would be helpful to allow us to sort by amount when doing an inquiry on Vendor Transactions and/or General Ledger Transactions. If you want to be able to sort by amount, try searching for your transactions using SmartList instead of the inquiry screens. -- Victoria Yudin Microsoft MVP - Great Plains "" <> wrote in message > It would be helpful to allow us to sort by amount when doing an inquiry on > Vendor Transactions and/or General Ledger T...

Sort GAL by last name
Hi, I have the following issue, which I doubt is uncommon. I have people who are in AD by one name but are known across campus as another. So when people try to find them in the GAL they are not able to unless they know the person's actual name. What I would like to do is sort the GAL by last name, first name. Is there anyway to do this? Or, does anyone have another suggestion how to fix this problem? This is on Exchange 2003 In, John Bodden <John> typed: > Hi, > > I have the...

Issues with 3-Column Sort
I have a user that is having a problem with a spreadsheet when doing a 3 column sort. We are using Excel 2003. Below is a description of the problem and what we have tried so far. If anyone has any ideas, I would great appreciate them. Thank you, JW Problem: Can not do one particular 3-column sort. Selecting to sort by column A, then by B, then by C does not work. Sorting by A itself works. Sorting by B, then by C does. I have it narrowed down to if we include column A in a multiple column sort, it fails. What we have tried: 1) Copied the cells from one sheet to another. The prob...

Sorts don't save?
An using MS Office 2002 Excel. Have various worksheets that I always sort a certain way... let's say address list is sorted by Column B, then Column A, then Column C...and no header row. Problem is... everytime I go to sort, I have to RE-ENTER this. Is there a way to save it for a specific worksheet file? Not sure, but it seems like some worksheets save it (it pops up with this sort criteria already in tact), and others don't. (Already tried installing all latest patches/updates) Would appreciate ideas or if this is way it's supposed to work and why. Thanks Maybe you co...

How do i sort contacts using more than one catagory ?
I am trying to create lists which have contacts sorted by more than one catagory. So including contacts that are linked to two or more specific catagories. Can this be done, if so how? thanks "samong" <> wrote in message >I am trying to create lists which have contacts sorted by more than one > catagory. So including contacts that are linked to two or more specific > catagories. Can this be done, if so how? The Category field is non-sortable because it is a multi...

How do I sort names Alphabelically in my lists
In the last Windows all I did was press "Sort" and it all happened. A change of Windows version shouldn't affect how Excel works. Which windows version have you changed from and to? Have you changed Excel versions as well? Which version of Excel were you using before, and which version are you using now? If your problem is in changing from Excel 3003 to 2007 you may wish to look at: or or

retain sort order while inserting item in a virtual list cotrol
Hi Everyone I am using virtual list control in my application. There are few item i add initially in the list. Now i sort the list control as per say size of the file. The question is suppose now i want to add one more item to list, how to retain the existing sort order of the CListCtrl and insert that item at a right position. E.g. Exactly like Microsoft outlook does. many thanks in advance Kamlesh Insert it into the data array, list, or whatever you are using to implement the data part of the virtual listbox, in order, and then invalidate from the position of the predecessor+1 to the end....

unable to sort in Select Names box
I just migrated from 98se/OL 2000 to XP proff/OL 2002. Now when I open a new email and click the "To..." button, and it opens the "Select Names" box, and I go to select a recipients email the fields won't let me sort like I used to. Plus there are additional entries with fax numbers, how do get it to sort and NOT display the fax number entries? Also, is there any way I can make this "Select Names" box bigger so I don't have to scroll right to see the if it is the correct email address if they have multiple email address's? Thanks Ted No...

optional parameter for query
I have a query and would like to have optional parameters for the users. Because I have more than 10 optional paramters which MS Access gives me query too complex and does not run it. I wonder that are there any easier way to have more than 10 optional parameters for users. I use "[forms]![MyForm]![MyDropDown] or [forms]![MyForm]![MyDropDown] is null" in criteria field. MS Access creates a very long SQL. I think it is why MS Access complained the SQL too complex. any information is great appreciated, Try Like IIF(IsNull([forms]![MyForm]![MyD...

Sort search results by date
It would be nice if i could sort my search results in this forum by date. I would like to see my most recent questions at the top. -- Sheri Salomone THANKS! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.

sorting by last char
Hello, I have a table where in a column there is something like: 1a, 1b, 1c, 2a, 2b, 2c and so on I wish to sort by the last char to look like: 1a 2a 1b 2b 1c 2c I tried Tools/Options/Custom List using *a,*b,*c to no avail. Any ideas? Thanks You can extract that last character into a helper column using: =3DRIGHT(A1) and copying down as needed. Then you should include the helper column within the data range when you sort, and sort on the helper first, followed by your other column. Delete the helper column when you've finished with it. Hope this helps. Pete On Sep 18, 12:52=A0am,...

Create a sort button?
I think I have seen this before somewhere but I cannot see an easy way to do it. I want to set up a basic spreadsheet with several columns and I would like to be able to sort them with just a pulldown or something in the header row. Any ideas or suggestions? Thanks. John In Excel 2003, select a cell in the list, and choose Data> List > Create List From the dropdown arrow at the top of each column, select Sort Ascending or Sort Descending. For earlier versions, you can use programming, as Dave Peterson describes here: jmpruitt wrote...

How do I sort tabs automatically?!
Excel2002/Win2K Hi Anyone know an automatic way to sort the tabs (of sheets within a workbook) into alphabetical order? Obviously one can drag them manually but when you have a lot of tabs (and you cant see them all on one screen) this becomes a damned nuisance. Is there a way to do it automatically using some tool? Ship Shiperton Henethe Hi have a look at the following site for some code to do this -- Regards Frank Kabel Frankfurt, Germany Shiperton Henethe wrote: > ...

Sort grouped unread email by date?
Outlook 2003. My unread email is grouped by folder. How can I get the mail in each folder to sort by date? I tried sorting by date, then folder, but the dates get scrambled after the folder sort. Thanks, Mich ...

Locking cell color pattern when sorting rows?
I am creating a spreadsheet where I want every alternating row to have a light grey solid pattern that acts as shading, so it is easier to follow individual rows from column to column on a printed page. The problem is that when I choose to sort rows by a specific data property in any column, the shading pattern becomes messed up, because there doesnt seem to be a way to lock the shaded pattern of each cell. Is there a solution to this problem? Thanks Sanjay; Might this be a solution ? Seperate lines between groups of data by using conditional formatting. - In a spreadsheet, build a table....

Sort And Total
Column A Is A Group Of Lengths, Many Are The Same Length Column B Indicates Components Of That Length Column C Indicates Different Components Of That Length A B C 8 2 1 7 2 0 8 3 1 6 2 2 8 1 1 7 1 1 I'd Like To Have Column A Sorted In Ascending Order With Colums B And C Displaying The Total Number Of Components Associated With That Length. I Would Like To Program The Columns To Perform The Function. E F G 6 2 2 7 3 1 8 6 3 Any Help Would Be Appreciated. Thanks, Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Sorting #46
Hey all. I have a column with users first name and last name inside the same single column What I want to do is split the data, into 2 columns and I can do this. Problem is, not all the data is the same, for instance John Doe Dr John Doe John Doe III etc... Therefore, I am wondering if there is a way to sort the data so the column will show the fields with FirstName Lastname first and then sort the exceptions in no parciular order. Thanks Caden -- Caden ------------------------------------------------------------------------ Caden's Profile:

Outlook 2003 / BCM
When you pull up the Opportunities page in Outlook 2003 / BCM - it sorts the stages alphabetically - (e.g. Closed Lost, Negotiation etc.). Is there a way for the view to open with the stages sorted by "stage" - (e.g. Prospecting, Qualification, Needs Analysis, etc.)? Please advise, BSW ...

It is a permission issue! And I've got it sorted
Finally got to the root of the problem... Request for the permission of type System.Net.WebPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed. inside the GetEntity method Fixed it by following: public override object GetEntity(Uri absoluteUri, string role, Type ofObjectToReturn) { if(absoluteUri.Scheme == "http") { System.Net.WebPermission p = new System.Net.WebPermission(System.Security.Permissions.PermissionState.Unrestricted); p.Assert(); return base.GetEntity(absoluteUri, role, ofObjectToReturn); ...