Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
organize a list of names in alphabetical order | Excel Discussion (Misc queries) | |||
How may I make a list display in alphabetical order in Excel? | Excel Discussion (Misc queries) | |||
How do I get my Excel document list in alphabetical order | Setting up and Configuration of Excel | |||
how do i sort excel worksheets by alphabetical order? | Excel Worksheet Functions | |||
how can i sort excel worksheets in alphabetical order? | Excel Worksheet Functions |