How to sort names in alphabetical ascending order?

  • Follow


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
Reply Utf 4/18/2010 7:10:01 AM

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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply Billns 4/18/2010 8:22:58 PM

7 Replies
1983 Views

(page loaded in 0.13 seconds)


Reply: