Alphabetized reference to a list
 

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?

Alphabetized reference to a list
 

One idea that could deliver the "double" you ordered, viz. auto-sorted by the
leftmost character, and with blanks removed in Sheet2 ..

Source list in Sheet1's col A, from row1 down

In Sheet2,
In A1:
=IF(Sheet1!A1="","",CODE(LEFT(TRIM(Sheet1!A1)))+RO W()/10^10)

In B1:
=TRIM(IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,MATC H(SMALL(A:A,ROW()),A:A,0))))
Copy A1:B1 down to cover the max expected extent of data in Sheet1's col A,
eg down to A100 (or more). Minimize/hide away col A. Col B returns the
results that you seek, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KenF" wrote:
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?

Alphabetized reference to a list
 

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?

Alphabetized reference to a list
 

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?

Alphabetized reference to a list
 

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?

Alphabetized reference to a list
 

*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?

Alphabetized reference to a list
 

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?