Using Excel Functions (Array) to put a list in alphabetical order
 

Everyday I have to update several lists.
They arrive to me on an excel spreadsheet in random order.
They are in one column with spaces mixed in throughout the list.

I want to be able to cut and paste those lists into my spreadsheet and
have them automatically re-order themselves in alphabetical order and
have the blank cells ignored.

I know about Excel's SORT function. I have been using it for years.
It will save me about 12 mins each morning if I can skip that step.
(Which would save me a workweek each year.)


I have gotten this far using an array:


=INDEX($A$1:$A$99,MATCH(SMALL(COUNTIF($A$1:$A$99," <"&$A$1:$A$99),ROW
(B1)),COUNTIF($A$1:$A$99,"<"&$A$1:$A$99),0))

This sorts the list in column A. The problem is the blank cells
appear at the top pf the list. I need the list to start in B1 and
the blank cells to be ignored.


I don't want to use VBA or a Macro to do it either.

Thanks,

Dan

Using Excel Functions (Array) to put a list in alphabetical order
 

This array formula** will sort *text only* and ignore empty cells. I'm also
assuming you don't have any "unusual" entries in your range like: < * ?.

=INDEX(A$1:A$15,MATCH(SMALL(IF(A$1:A$15<"",COUNTI F(A$1:A$15,"<"&A$1:A$15)),ROWS(B$1:B1)),IF(A$1:A$1 5<"",COUNTIF(A$1:A$15,"<"&A$1:A$15)),0))

Copy down until you #NUM! errors. Or, if you want an error trap:

=IF(ROWS(B$1:B1)<=COUNTA(A$1:A$15),INDEX(A$1:A$15, MATCH(SMALL(IF(A$1:A$15<"",COUNTIF(A$1:A$15,"<"&A $1:A$15)),ROWS(B$1:B1)),IF(A$1:A$15<"",COUNTIF(A$ 1:A$15,"<"&A$1:A$15)),0)),"")

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

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Everyday I have to update several lists.
They arrive to me on an excel spreadsheet in random order.
They are in one column with spaces mixed in throughout the list.

I want to be able to cut and paste those lists into my spreadsheet and
have them automatically re-order themselves in alphabetical order and
have the blank cells ignored.

I know about Excel's SORT function. I have been using it for years.
It will save me about 12 mins each morning if I can skip that step.
(Which would save me a workweek each year.)


I have gotten this far using an array:


=INDEX($A$1:$A$99,MATCH(SMALL(COUNTIF($A$1:$A$99," <"&$A$1:$A$99),ROW
(B1)),COUNTIF($A$1:$A$99,"<"&$A$1:$A$99),0))

This sorts the list in column A. The problem is the blank cells
appear at the top pf the list. I need the list to start in B1 and
the blank cells to be ignored.


I don't want to use VBA or a Macro to do it either.

Thanks,

Dan

Using Excel Functions (Array) to put a list in alphabetical order
 

Copy down until you #NUM! errors. Or, if you want an error trap:

=IF(ROWS(B$1:B1)<=COUNTA(A$1:A$15),INDEX(A$1:A$15, MATCH(SMALL(IF(A$1:A$15<"",COUNTIF(A$1:A$15,"<"&A $1:A$15)),ROWS(B$1:B1)),IF(A$1:A$15<"",COUNTIF(A$ 1:A$15,"<"&A$1:A$15)),0)),"")

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


That worked out great. However I bring in all of the names I am using
to the front page of my worksheet using references to other
workpages. So the "blank" cells really arn't blank they have a
formula reference that is refering to a blank cell. The result is
the Array alphabetizes it perfectly but all cells which refer to a
cell that is blank are at the bottom of the array resulting in
#NUM! .

Is there a way to have those cells in the array which refer to blank
cells also be "blank"?

Thanks so much,

Dan

Using Excel Functions (Array) to put a list in alphabetical order
 

Replace this:

COUNTA(A$1:A$15)

With this:

COUNTIF(A$1:A$15,"?*")

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...

Copy down until you #NUM! errors. Or, if you want an error trap:

=IF(ROWS(B$1:B1)<=COUNTA(A$1:A$15),INDEX(A$1:A$15, MATCH(SMALL(IF(A$1:A$15<"",COUNTIF(A$1:A$15,"<"&A $1:A$15)),ROWS(B$1:B1)),IF(A$1:A$15<"",COUNTIF(A$ 1:A$15,"<"&A$1:A$15)),0)),"")

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


That worked out great. However I bring in all of the names I am using
to the front page of my worksheet using references to other
workpages. So the "blank" cells really arn't blank they have a
formula reference that is refering to a blank cell. The result is
the Array alphabetizes it perfectly but all cells which refer to a
cell that is blank are at the bottom of the array resulting in
#NUM! .

Is there a way to have those cells in the array which refer to blank
cells also be "blank"?

Thanks so much,

Dan