| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why are my Excel columns numbered and not alphabetized (A..ZZ)? | Excel Discussion (Misc queries) | |||
| Why are my Excel columns numbered and not alphabetized (A..ZZ)? | Excel Discussion (Misc queries) | |||
| Items in drop-down list alphabetized | Excel Worksheet Functions | |||
| Searching an Alphabetized list for data, then RESORT | Excel Worksheet Functions | |||
| How do you list the numbers in a 3d reference in excel? | Excel Worksheet Functions | |||
Hybrid Mode
