Alphabetized reference to a list

K

KenF

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

Max

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)))+ROW()/10^10)

In B1:
=TRIM(IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(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.
 
T

T. Valko

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(SMALL(IF(rng<>"",COUNTIF(rng,"<"&rng)),ROWS(A$1:A1)),IF(rng<>"",COUNTIF(rng,"<"&rng)),0)),"")

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

KenF

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

T. Valko

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(SMALL(COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),ROWS(A$1:A1)),COUNTIF(rng,"<"&rng&"")+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)
 
K

KenF

*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

T. Valko

You're welcome!

--
Biff
Microsoft Excel MVP


KenF said:
*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!
Click to expand...
 
Top