Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 18th 10, 08:10 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,670
Default 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

  #2   Report Post  
Old April 18th 10, 08:38 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,670
Default How to sort names in alphabetical ascending order?

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

  #3   Report Post  
Old April 18th 10, 08:54 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 132
Default How to sort names in alphabetical ascending order?

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

  #4   Report Post  
Old April 18th 10, 09:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,670
Default How to sort names in alphabetical ascending order?

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

  #5   Report Post  
Old April 18th 10, 10:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 132
Default How to sort names in alphabetical ascending order?

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



  #6   Report Post  
Old April 18th 10, 11:45 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 132
Default How to sort names in alphabetical ascending order?

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

  #7   Report Post  
Old April 18th 10, 03:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,670
Default How to sort names in alphabetical ascending order?

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

  #8   Report Post  
Old April 18th 10, 09:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 84
Default How to sort names in alphabetical ascending order?

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highest then alphabetical order By Names? Killer Excel Discussion (Misc queries) 0 September 24th 07 03:20 AM
How to sort worksheets in alphabetical order (descending or ascending)? [email protected] Excel Discussion (Misc queries) 2 June 14th 07 10:13 PM
sort last names by in alphabetical order Jackie Excel Discussion (Misc queries) 1 May 24th 06 08:45 PM
Sort other than by alphabetical ascending/ descending Melissa Excel Discussion (Misc queries) 6 September 2nd 05 07:25 AM
Formula to sort names in alphabetical order? Fredrik Jonsson Excel Discussion (Misc queries) 0 September 1st 05 04:33 PM


All times are GMT +1. The time now is 03:47 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017