How to sort names in alphabetical ascending order?

There is a table from cell M2 to W200, which contains names and empty cell.
I would like to retrieve the names and sort them in in alphabetical 
ascending order under Z column.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric
0
Utf
4/18/2010 7:10:01 AM
excel.misc 78881 articles. 3 followers. Follow

7 Replies
3216 Views

Similar Articles

[PageSpeed] 33

N.B. how can I sort them using formula, such as large or small function
Thanks in advance for any suggestions
Eric

"Eric" wrote:

> There is a table from cell M2 to W200, which contains names and empty cell.
> I would like to retrieve the names and sort them in in alphabetical 
> ascending order under Z column.
> Does anyone have any suggestions on how to do it in excel?
> Thanks in advance for any suggestions
> Eric
0
Utf
4/18/2010 7:38:01 AM
Large and Small functions only work with the Numbers and it doesn't work with 
Text Characters.

--------------------
(Ms-Exl-Learner)
--------------------


"Eric" wrote:

> N.B. how can I sort them using formula, such as large or small function
> Thanks in advance for any suggestions
> Eric
> 
> "Eric" wrote:
> 
> > There is a table from cell M2 to W200, which contains names and empty cell.
> > I would like to retrieve the names and sort them in in alphabetical 
> > ascending order under Z column.
> > Does anyone have any suggestions on how to do it in excel?
> > Thanks in advance for any suggestions
> > Eric
0
Utf
4/18/2010 7:54:01 AM
Do you have any suggestions on what kind of  functions work with text 
characters?
Thanks in advance for any suggestions
Eric

"Ms-Exl-Learner" wrote:

> Large and Small functions only work with the Numbers and it doesn't work with 
> Text Characters.
> 
> --------------------
> (Ms-Exl-Learner)
> --------------------
> 
> 
> "Eric" wrote:
> 
> > N.B. how can I sort them using formula, such as large or small function
> > Thanks in advance for any suggestions
> > Eric
> > 
> > "Eric" wrote:
> > 
> > > There is a table from cell M2 to W200, which contains names and empty cell.
> > > I would like to retrieve the names and sort them in in alphabetical 
> > > ascending order under Z column.
> > > Does anyone have any suggestions on how to do it in excel?
> > > Thanks in advance for any suggestions
> > > Eric
0
Utf
4/18/2010 8:27:01 AM
Assume that you are having the below data like the below:-

Row / Col	A Column
Row1	NAME
Row2	NDF
Row3	ABC
Row4	YJK
Row5	LDF
Row6	NTK
Row7	PMR
Row8	OTF
Row9	KQY
Row10	DFT
Row11	PKR

In B2 cell paste the below formula
=COUNTIF($A$2:$A$11,"<="&$A2)
Copy the B2 cell and paste it upto B11.

In C2 cell paste the below formula
=INDEX($A$2:$A$11,MATCH(ROW()-ROW($C$1),$B$2:$B$11,FALSE))
Copy the C2 cell and paste it upto C11.

Now the data will look like the below

Row / Col	A Column	B Column	C Column
Row1	NAME	NUMBER	RESULT TEXT
Row2	NDF	5	ABC
Row3	ABC	1	DFT
Row4	YJK	10	KQY
Row5	LDF	4	LDF
Row6	NTK	6	NDF
Row7	PMR	9	NTK
Row8	OTF	7	OTF
Row9	KQY	3	PKR
Row10	DFT	2	PMR
Row11	PKR	8	YJK

Hope this is what you are expecting to do…

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Eric" wrote:

> Do you have any suggestions on what kind of  functions work with text 
> characters?
> Thanks in advance for any suggestions
> Eric
> 
> "Ms-Exl-Learner" wrote:
> 
> > Large and Small functions only work with the Numbers and it doesn't work with 
> > Text Characters.
> > 
> > --------------------
> > (Ms-Exl-Learner)
> > --------------------
> > 
> > 
> > "Eric" wrote:
> > 
> > > N.B. how can I sort them using formula, such as large or small function
> > > Thanks in advance for any suggestions
> > > Eric
> > > 
> > > "Eric" wrote:
> > > 
> > > > There is a table from cell M2 to W200, which contains names and empty cell.
> > > > I would like to retrieve the names and sort them in in alphabetical 
> > > > ascending order under Z column.
> > > > Does anyone have any suggestions on how to do it in excel?
> > > > Thanks in advance for any suggestions
> > > > Eric
0
Utf
4/18/2010 9:51:01 AM
To get it in a single formula then use the below:-

Row / Col	A Column
Row1	NAME
Row2	NDF
Row3	ABC
Row4	YJK
Row5	LDF
Row6	NTK
Row7	PMR
Row8	OTF
Row9	KQY
Row10	DFT
Row11	PKR

Copy and paste the below formula in B2 cell

=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))

Place the cursor in B2 cell and press F2 and give Cntrl+Shift+Enter, since 
it is an array formula.  

Copy the B2 cell and paste it to the remaining cells of B Column depends 
upon the A column data.
-- 
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Eric" wrote:

> Do you have any suggestions on what kind of  functions work with text 
> characters?
> Thanks in advance for any suggestions
> Eric
> 
> "Ms-Exl-Learner" wrote:
> 
> > Large and Small functions only work with the Numbers and it doesn't work with 
> > Text Characters.
> > 
> > --------------------
> > (Ms-Exl-Learner)
> > --------------------
> > 
> > 
> > "Eric" wrote:
> > 
> > > N.B. how can I sort them using formula, such as large or small function
> > > Thanks in advance for any suggestions
> > > Eric
> > > 
> > > "Eric" wrote:
> > > 
> > > > There is a table from cell M2 to W200, which contains names and empty cell.
> > > > I would like to retrieve the names and sort them in in alphabetical 
> > > > ascending order under Z column.
> > > > Does anyone have any suggestions on how to do it in excel?
> > > > Thanks in advance for any suggestions
> > > > Eric
0
Utf
4/18/2010 10:45:01 AM
I try it, which works if all data is under the same column, but it does not 
work for table from M2 to W200. Do you have any suggestions on how to make it 
work for a table?
Thank you very much for any suggestions
Eric

"Ms-Exl-Learner" wrote:

> To get it in a single formula then use the below:-
> 
> Row / Col	A Column
> Row1	NAME
> Row2	NDF
> Row3	ABC
> Row4	YJK
> Row5	LDF
> Row6	NTK
> Row7	PMR
> Row8	OTF
> Row9	KQY
> Row10	DFT
> Row11	PKR
> 
> Copy and paste the below formula in B2 cell
> 
> =INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),ROW(1:1)),COUNTIF($A$2:$A$11,"<"&$A$2:$A$11),0))
> 
> Place the cursor in B2 cell and press F2 and give Cntrl+Shift+Enter, since 
> it is an array formula.  
> 
> Copy the B2 cell and paste it to the remaining cells of B Column depends 
> upon the A column data.
> -- 
> Remember to Click Yes, if this post helps!
> 
> --------------------
> (Ms-Exl-Learner)
> --------------------
> 
> 
> "Eric" wrote:
> 
> > Do you have any suggestions on what kind of  functions work with text 
> > characters?
> > Thanks in advance for any suggestions
> > Eric
> > 
> > "Ms-Exl-Learner" wrote:
> > 
> > > Large and Small functions only work with the Numbers and it doesn't work with 
> > > Text Characters.
> > > 
> > > --------------------
> > > (Ms-Exl-Learner)
> > > --------------------
> > > 
> > > 
> > > "Eric" wrote:
> > > 
> > > > N.B. how can I sort them using formula, such as large or small function
> > > > Thanks in advance for any suggestions
> > > > Eric
> > > > 
> > > > "Eric" wrote:
> > > > 
> > > > > There is a table from cell M2 to W200, which contains names and empty cell.
> > > > > I would like to retrieve the names and sort them in in alphabetical 
> > > > > ascending order under Z column.
> > > > > Does anyone have any suggestions on how to do it in excel?
> > > > > Thanks in advance for any suggestions
> > > > > Eric
-1
Utf
4/18/2010 2:34:01 PM
On 4/18/2010 12:10 AM, Eric wrote:
> There is a table from cell M2 to W200, which contains names and empty cell.
> I would like to retrieve the names and sort them in in alphabetical
> ascending order under Z column.
> Does anyone have any suggestions on how to do it in excel?
> Thanks in advance for any suggestions
> Eric

The brute force approach would be to copy and paste each column M to W 
to column Z individually, then sort alphabetically in column Z. If you 
don't need to retain the names in the table you could cut and paste instead.

Bill
0
Billns
4/18/2010 8:22:58 PM
Reply:

Similar Artilces:

Where's the Sort by New Message option?
Not sure when the layout of the newsgroups changed but I can't seem to find the ability to sort by new message in a thread instead of sorting by just the original thread date. That was hugely helpful as I could follow along and see what threads were still active. If you're gonna hang around in the newsgroups (any newsgroups), you may want to start using a newsreader. Microsoft Outlook Express can do it for you. Saved from a previous post: You may want to connect to the ms newsserver directly: If you have Outlook Express installed, try clicking on these links (or copy and paste i...

How to sort account list?
I'm using MS Money 2004 Small Business. In the account list, my accounts are catagorized by and in this order: - bank accounts - credit accounts - investment accounts - loans and liabilities - asset accounts TOTAL BALANCE In the above view, I cannot easily determine totals for assets and liabilities. The following view does this. - bank accounts - investment accounts - asset accounts SUBTOTAL -loans and liabilities SUBTOTAL TOTAL BALANCE How do I get the above view? Thanks, Brett I don't use Small Business. But you can run a Net Worth report to get total assets and ...

Sorting
Hi, is it possible to use variable for sorting? Something like: declare @sort varchar(10) SET @sort='column1 DESC' SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table Is dynamic SQL only option? Thank you, Simon On 15/04/2010 11:28, simon wrote: > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? You can use CASE to he...

Row names will not move with sort
Each row in my summary table in Sheet1 is hyperlinked to a corresponding table in Sheet2 with additional details. I need to move the rows in my summary table (Sheet1) and preserve the links to and from the details tables (Sheet2). I found that I can move rows in Sheet1 with cut & insert and the hyperlinks are maintained. I can travel from a row in summary table (Sheet1) to details table (Sheet2) and back, even after the corresponding row (Sheet1) was moved. But if I sort the rows in Sheet1 the links from Sheet2 back to corresponding rows in Sheet1 lead to presorted locations. Apparen...

Formula to return the name of a tab?
We have a spreadsheet set up that pulls the minimum value from several different tabs. We are looking for a formula or a way to display, in the next column, which tab that minimum value came from. Does anyone have any ideas? You might check the Help files for information on the SHEETNAME function. Ed "Amanda" <anonymous@discussions.microsoft.com> wrote in message news:54F7CB4F-A621-4DB0-9963-56F0406EE8F1@microsoft.com... > We have a spreadsheet set up that pulls the minimum value from several different tabs. We are looking for a formula or a way to display, in the next...

Creating A Chart that counts repitition (don't know name)
So, I feel like a moron not knowing this. But I must have had a brain freeze for the last week. I have a tableset of about 300 rows in Excel 2007 with one column being a name and the other being a year. I want to create a graph of the years. IE Y Axis = number of times a certain year appears in the tableset column and the x axis being the years between (year k and year m). Sounds like a histogram. First you need to compile the data, then make a chart of the compiled data. Here's how: http://peltiertech.com/Excel/ChartsHowTo/ConsolidateData.html - Jon ------- Jon Peltier, Microso...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David...

sorting macro #3
I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a time but I know there must be an easier way. Can ANYONE HELP? C3:J3 I need these cells sorted and then down to C1532:J1532 I did create a macro to sort after I selected them but still one at a time. Does it have to be a macro? I can edit a macro but don't really know how to program them. Yes, it has to be a macro. The following should do it. This macro loops through all the entries in Column C, and in each row it sorts the values in Columns C:J. HTH Otto Sub SortRows() Dim rColC As Range Dim i As...

how2: show contact name and phone fields in task list
It would be useful if I could display contact name and phone number fields in my task list. In Simple View I've used Field Chooser to insert these columns into the table .. but alas! the fields are blank. Can I do what I want to do and .. how? thanks Any folder view shows only the data in that folder, even though Outlook lets you think otherwise by allowing you to add fields from different types of items in the folder. You may be able to accomplish what you want, however, with a little custom VBA code. For an example, see http://www.outlookcode.com/codedetail.aspx?id=566 -- Su...

Can we sort a form and also be able to enter new records.
Hi, It is possible to sort the data on a form and also be able to enter new and edit the existing records in it. Cheers! On Tue, 15 Jan 2008 15:41:16 -0800 (PST), Rex wrote: > Hi, > > It is possible to sort the data on a form and also be able to enter > new and edit the existing records in it. > > Cheers! I assume you mean "Is it". The form sort order has nothing to do with editing or adding new records, so the answer is yes. If you cannot edit or add new records, make sure the form's record source, if it is a query, is updateable. Some queries are not. O...

Sorting checking register
How do i sort my check register in Money 2004 Deluxe so that uncleared transactions appear below cleared transactions? In Quicken i could click on the column to provide that sort. In microsoft.public.money, Dean wrote: >How do i sort my check register in Money 2004 Deluxe so >that uncleared transactions appear below cleared >transactions? In Quicken i could click on the column to >provide that sort. You don't. Available choices are chosen by clicking View at the top of the register. ...

Method Naming
Hello, This might be a strange question but I am creating a few repositories and I am looking how should I name my methods. I have been looking in C# and I came up with the following: public interface IAlbumRepository { void Add(Album album); IQueryable<Album> FindAll(); Album FindById(Int32 id); void Remove(Int32 id); void Save(); void Update(Album album); } // IAlbumRepository What do you think? Previously I had "Create", "Delete", etc. But I think this is maybe closer to what is usually done. Not? Thanks, Mi...

using addrress books to sort incoming mail
I use address books to sort incoming mail. An odd thing: depending on how the sender's name comes across, it doesn't get caught by the filter (amd moved to the "Family Mail" Folder I have set up. for example: my mom and dad's email (from momanddad@somedomain.com) comes as from that address. However, in my contacts, they are listed by their names: John and Mary Smith, with momanddad@somedomain.com as their email address and John and Mary Smith as the display name. What have I done wrong? TIA 1. The big ONE - failed to post your version of Outlook. Rules operate at ...

Unmerge so I can sort
I coy/pasted data into Excell and when I try to sort it I get "This operation requires merged sells to be identically sized". I understadn the work around in KB but how do I find which sells are merged? I wouldn't bother looking. I'd just remove all the merged cells from the range to sort. Select that range format|cells|alignment tab uncheck the merge cells box But if you wanted to search, you can do it via process of elimination. Select a range, format|cells|alignment tab. If that merge cells box is checked, then the whole selection is merged. If it's grey, t...

Rule @ script order
Hi there Trying to create a rule using the rule wizard, where a script runs first and the item copy is second. It is insisting on running the copy part before the script. Anyway to fix this? -- Adam S. If it won't work as you desire in the rule, you will need to create two rules- first one with the script - second one with the copy. The first rule should not use stop processing action. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@list...

How do I get Outlook to automatic load a name from my contact lis.
I can not get Outlook to automatic load a name from my contact list. It keeps loading names and addresses from the old Outlook Express address book, which has been deleted? Outlook cannot do that so you are mistaken. A clear description of what you're doing and what you're seeing is necessary for anyone to help you. A term like "keeps loading names" is meaningless to anyone not watching you. Always include your version when posting a question. -- Russ Valentine "njrj" <njrj@discussions.microsoft.com> wrote in message news:5A5E9522-92BC-4528-...

sorting issue
Hi, When a range of columns are highlighted and a sort is performed on only one column empty cells above the sort display first. How can this be prevented. I know when there is data in any of the other column but not on the column being sorted this will happen. Any thoughts? Pat Are you sure the cells are really empty? They don't contain spaces or even formulas that evaluate to "". (Even formulas converted to values that returned "")? Pat wrote: > > Hi, > > When a range of columns are highlighted and a sort is performed on only one > column em...

Domain Trust to 2 domains with the same NetBIOS name
Hi, I am banging my head against the wall with this one and really need some help.... Scenario:- I have 3 AD domains as follows:- Domain 1 AD namespace - uk.company.local netbios domain name = ukcompany 2003 finctional forest and domain Domain 2 AD namespace - france.company.local netbios domain name = company **same as domain 3 2000 finctional forest and domain Domain 3 AD namespace - spain.company.local netbios domain name - company **same as domain 2 2000 finctional forest and domain Is there any way that Domain 1 can create a Trust to domain 2 and domain 3 ...

Sort not sorting all rows
I'm using Excel 2000. have created a document, uploaded it to google docs and spreadsheets, exported it as a doc, converted it to a spreadsheet (excel) and uploaded it again to google docs. It's not a complicated file - no calculations or anything, just a simple log to enable us to collaborate on change requests and website faults. New requests are assigned a number incrementally one higher than the previous. So we need to sort on the Log number column to see what the last logged number was. However, it sorts most of the spreadsheet and leaves the same 5 rows unsorted at the...

Sort date by day and not year
Need to sort birthdates by month and day; ignore year. Ex: Would like to sort like list below: 3/02/1997 3/15/2002 3/21/1996 Not like this: 3/21/1996 3/02/1997 3/15/2002 Please help and thank you for your help. Kathy Hi one way: - add an additional helper column (lets ay B) - insert the following formula in B1 (if column A stores your list): =MONTH(A1) - copy this formula for all rows - sort with this helper column -- Regards Frank Kabel Frankfurt, Germany Kathy wrote: > Need to sort birthdates by month and day; ignore year. > > Ex: > Would like to sort like list below: >...

Sort and exclude certain words
Hi ... I work in a public library and frequently export lists of books from our online catalog to Excel, so that I can manipulate the data in various ways. My problem in sorting in Excel is this: Our library catalog excludes words like "The" "A" or "An" at the beginning of a title when sorting. Excel, however, does not. Is there any way I can sort on a field in Excel and have it ignore certain words at the beginning of a string? Thanks! -- jds217 ------------------------------------------------------------------------ jds217's Profile: http://www.exc...

Not sorting as expected
I have a database some 10 columns wide and 1200 rows deep. When I sort by one particular column it sorts BLANKS before text meaning that the data appears at the bottom of the database although still in alphabetical order. I have the formatting set to GENERAL for the whole column and do want the blank cells. None of the other columns do this. I am totally perplexed as I thought Excel sorted blanks nearer the end of it's priority. I have tried formatting the column to TEXT and even NUMBER also but it still makes the data appear at the bottom of the table. This doesn't make any se...

VBA Format for a Range Name Used in the Sum Function
I am trying to run some VBA code that I would use Range names rather than a specific range, ie Data!M3:M223. MsgBox Evaluate("SUM( Data!M3:M223 )") will work, but when I change it to include the Range name representing Data!M3:M223, it does not. Can anyone help to figure out what I'm doing wrong. Thanks in advance. Dim mTimeCriteria As String Dim mPosition As String Dim mFormula As String Dim mQuestion1Range As Range mTimeCriteria = "First day of employment (Time 1)" mPosition = "Registered Nurse" mPositionRange = Worksheets("...

Reviewer name in Outlook message not user's name
Was this user's profile copied from a profile of a user who is no longer active? This is in Outlook 2000 SR-1. I assume that the name she sees when she forwards an email and begins typing is the user name that was associated with that PC? or with the Exchange profile setting? We are on Windows 2000 server and desktop. Thanks. -- Alice. ...