I'm not sure if this is possible. Does anyone know of a way to maintain two lists of data where items are added to to random locations within the first one and the second one always lists all items in alphabetical order ? (Without having to run anything to do it; i.e. with formulas and/or functions). Assume up to 300 items. There may be gaps of several rows of spaces in the first list, so it would have to be defined as going from row 1 to row 300, rather than by contiguous data. Thank you for any help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Ongoing alphabetized list
- Thread starter Sneech#7
- Start date
Hi Sneech#7
I have a solution for alphabetizing lists but it consists of big array formulas and it’s very slow for lists as big as yours, I tried your example of a list with 300 items, each one with 10 letters, and it takes more than 4 seconds to update each time you change one item.
It’s great and very practical for small lists though.
Anyway, I never saw a solution for alphabetizing lists published (maybe because I’m new at the board), so here is my solution:
Your list is in A1:A300
In B1:B300 you have your list sorted, with the blanks at the end as you wanted.
There are two formulas, one for B1 and another for B2:B300
B1=IF(ROWS($A$1:$A$300)=COUNTBLANK($A$1:$A$300),"",INDEX($A$1:$A$300,MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300)+ISBLANK($A$1:$A$300)*1000,0),1))
B2=IF(ROW()-ROW($A$1)>=ROWS($A$1:$A$300)-COUNTBLANK($A$1:$A$300),"",IF(ISERROR(MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300),0)),B1,INDEX($A$1:$A$300,MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300)+ISBLANK($A$1:$A$300)*1000,0),1)))
Copy B2 to the cells B3:B300
The formulas are big and ugly because I could not find an elegant solution that takes into account the case of several strings in your list being equal. If you are sure that your list never has duplicate entries you can simplify the formulas.
They are valid to a list of up to 1000 items,
I have a solution for alphabetizing lists but it consists of big array formulas and it’s very slow for lists as big as yours, I tried your example of a list with 300 items, each one with 10 letters, and it takes more than 4 seconds to update each time you change one item.
It’s great and very practical for small lists though.
Anyway, I never saw a solution for alphabetizing lists published (maybe because I’m new at the board), so here is my solution:
Your list is in A1:A300
In B1:B300 you have your list sorted, with the blanks at the end as you wanted.
There are two formulas, one for B1 and another for B2:B300
B1=IF(ROWS($A$1:$A$300)=COUNTBLANK($A$1:$A$300),"",INDEX($A$1:$A$300,MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300)+ISBLANK($A$1:$A$300)*1000,0),1))
B2=IF(ROW()-ROW($A$1)>=ROWS($A$1:$A$300)-COUNTBLANK($A$1:$A$300),"",IF(ISERROR(MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300),0)),B1,INDEX($A$1:$A$300,MATCH(ROW()-ROW($A$1),COUNTIF($A$1:$A$300,"<"&$A$1:$A$300)+ISBLANK($A$1:$A$300)*1000,0),1)))
Copy B2 to the cells B3:B300
The formulas are big and ugly because I could not find an elegant solution that takes into account the case of several strings in your list being equal. If you are sure that your list never has duplicate entries you can simplify the formulas.
They are valid to a list of up to 1000 items,
Upvote
0
Excel Facts
Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Guys, with all due respect, I know several VBA options... I'm wondering if there is a non-VBA solution that makes sense, if anyone has done this type of thing with cell functions.
| AlphanumericSortByMeansOfFormulas.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | 13 | |||||
| 2 | List | Ascending Order | Rank | SortedList | ||
| 3 | Mac | 8 | 7 | Access | ||
| 4 | Apple | 3 | 2 | Apple | ||
| 5 | Unix | 13 | 12 | eBay | ||
| 6 | Navision | 9 | 8 | Excel | ||
| 7 | Windows | 14 | 13 | Firefox | ||
| 8 | Excel | 5 | 4 | GNumeric | ||
| 9 | eBay | 4 | 3 | Mac | ||
| 10 | SAP | 11 | 10 | Navision | ||
| 11 | Navision | 9 | 9 | Navision | ||
| 12 | Access | 2 | 1 | SAP | ||
| 13 | Thunderbird | |||||
| 14 | Firefox | 6 | 5 | Unix | ||
| 15 | Thunderbird | 12 | 11 | Windows | ||
| 16 | GNumeric | 7 | 6 | |||
| 17 | ||||||
Sheet1 | ||||||
B3, copied down:
=IF(A3<>"",SUMPRODUCT((A3>$A$3:$A$16)+0)+1,"")
C3, copied down:
=IF(N(B3),RANK(B3,$B$3:$B$16,1)+COUNTIF($B$3:B3,B3)-1,"")
D1:
=MAX(C:C)
D3, copied down:
=IF(ROWS(D$3:D3)<=$D$1,INDEX($A$3:$A$16,MATCH(ROWS(D$3:D3),$C$3:$C$16,0)),"")
Upvote
0
Similar threads
- Question
