You're welcome!

--
Biff
Microsoft Excel MVP


"KenF" wrote in message
...
*Laugh* Thank you; this will do. And I liked the 100000*ISBLANK(rng)
instead
of IF(rng<"",...).

Now if only Excel had a SMALLA() function, this would be so much easier...

Thanks!

"T. Valko" wrote:

If there are numbers in the range it gets *really* complex!

This array formula** will list any numbers *first* (in ascending order)
then
the text:

=IF(ROWS(A$1:A1)<=COUNTA(rng),INDEX(rng,MATCH(SMAL L(COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+1 00000*ISBLANK(rng),ROWS(A$1:A1)),COUNTIF(rng,"<"&r ng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng), 0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"KenF" wrote in message
...
That is clever using COUNTIF(rng,"<"&rng).

One problem I ran across with this is that this counts Pure text (e.g.,
"ABC") and Numbers as text (e.g., "123") separately. Is there a way to
deal
with this? That is, list of text can include Names, dates (i.e.,
numbers),
Addresses, etc.

Thanks!


"T. Valko" wrote:

I have a list of text ... The list could contain empty fields

Enter this array formula** in Sheet2 A1 and copy down to A100:

rng = Sheet1$A$1:$A$100

=IF(ROWS(A$1:A1)<=COUNTA(rng),INDEX(rng,MATCH(SMAL L(IF(rng<"",COUNTIF(rng,"<"&rng)),ROWS(A$1:A1)),I F(rng<"",COUNTIF(rng,"<"&rng)),0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"KenF" wrote in message
...
I have a list of text ('Sheet1'!A1:A100). The list could contain
empty
fields, and the contents of this list could change.

1) I want to create a list in Sheet2 that is the above list
alphabetized.
(Changes in the original list must of course result in changes in
this
list)
2) Alternatively, I want to create a list in Sheet2 that removes all
blanks
(empty fields) or a least pushes them to the end of the list.

How do I do these?