Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Producing Sorted Alpha Listings by Formula (Excel 2K)

    Hi,

    I have an unsorted list of placenames to enter regularly, and I would like to be able to have an alphabetically sorted listing available by formula as the data is entered, such as Ranking for text.

    Is this possible?

    May be I am missing something!

    Any ideas?

    Regards,
    Peter Moran

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Producing Sorted Alpha Listings by Formula (Excel 2K)

    Let's say that you enter place names in A2, A3 etc. (A1 is a column heading), and that you won't go beyond A100.
    Enter the following formula in B2:

    =SUMPRODUCT((A$2:A$100<>"")*(A2>=A$2:A$100))

    And in C2:

    =IF(B2>0,INDEX(A$2:A$100,MATCH(ROW()-1,B$2:B$100,0)),"")

    Fill down B2:C2 to row 100.

    Column B is an auxiliary column, you can hide it.

    See attached sample workbook (Inspired by <post:=257,247>post 257,247</post:> by Pieterse)
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Producing Sorted Alpha Listings by Formula (Excel 2K)

    Hi,

    Thanks Hans and Jan.

    At least you confirmed my initial thoughts that there was no easy way I was overlooking.

    Also found Jan's other thread you referred to most interesting.

    Have already implemented your solution, Hans, but will also look at the workbook.

    Thanks again.

    Regards,
    Peter Moran

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Producing Sorted Alpha Listings by Formula (Ex

    Using Han's table

    A non-array formula and without auxiliary column

    Cell D2, entered the formula and copied down :

    =LOOKUP(2,1/(COUNTIF($A$2:$A$101,"<="&$A$2:$A$101)=ROW(1:1)),$ A$2:$A$101)

    Regards
    Bosco

Posting Permissions

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