+ Reply to Thread
Results 1 to 4 of 4

Sort text list alphabetically using a formula

  1. #1
    Registered User
    Join Date
    12-19-2005
    Posts
    2

    Question Sort text list alphabetically using a formula

    Does anyone know of a formula you can use to sort a list of text alphabetically?

    I know the data/sort option but I want something that can pick up new entries in the original data list without the use having to intervene.

    Seems like a straightforward enough idea but I can't find anything.

  2. #2
    Domenic
    Guest

    Re: Sort text list alphabetically using a formula

    First, set up a dynamic range for your list of text and give it a name,
    such as MyRange. Then enter the following formula in a cell, let's say
    B1, and copy down:

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

    ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Post back if you
    need help creating a dynamic range.

    Hope this helps!

    In article <[email protected]>,
    paddyyates <[email protected]>
    wrote:

    > Does anyone know of a formula you can use to sort a list of text
    > alphabetically?
    >
    > I know the data/sort option but I want something that can pick up new
    > entries in the original data list without the use having to intervene.
    >
    > Seems like a straightforward enough idea but I can't find anything.


  3. #3
    Registered User
    Join Date
    12-19-2005
    Posts
    2
    That works, thanks. It seems the answer wasn't that straightforward!

  4. #4

    Re: Sort text list alphabetically using a formula

    A somewhat simpler method, requiring helper columns. Assume your text
    in A:A.

    In B1: = IF(LEN(A1)>0,COUNTIF(A:A,"<"&A1),"")
    gives a lexographical sorting number to each entry

    In C1: = IF(LEN(A1)>,SMALL(B:B,ROW()),"")
    Sorts the values.

    In D1: = IF(LEN(A1)>0,INDEX(A:A,MATCH(C1,B:B,0),1),"")

    Fill down past your data. Can continue in this way to remove dupes.

    ....best, Hash

    In article <[email protected]>,
    paddyyates <[email protected]>
    wrote:

    > Does anyone know of a formula you can use to sort a list of text
    > alphabetically?
    >
    > I know the data/sort option but I want something that can pick up new
    > entries in the original data list without the use having to intervene.
    >
    > Seems like a straightforward enough idea but I can't find anything.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1