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

1. ## 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. ## 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)

3. ## 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. ## 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
•