Sort a list alphabetically forcing one entry to the top

[Krishna Khemraj]

I've used http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/
to create a method to sort a list using formulas and it works great.
I use the following to rank the entries then use a VLOOKUP and ROW to sort the data.
=IF(COUNTIF(range,"<="&A4)=0,0,COUNTIF(range,"<="&A4))
Does anyone have a method to force one entry to the top of the list?

Albert
All
Fred
Linda
Mary
Tina

I would like the All to be at the top.

 

[prasaddn]

see the attached file.

Regards,
Prasad DN

 

[Nebu]

Hi

Try this formula =IF(A1="All",1,IF(COUNTIF($A$1:$A$6,"<="&A1)=1,COUNTIF($A$1:$A$6,"<="&A1)+1,COUNTIF($A$1:$A$6,"<="&A1)))

I have hard coded "ALL". I am not sure is there any better way of doing this, but I am getting the result you were looking for . Wait for this space someone else will be having a better logic.

Thanks
Nebu

 

[prasaddn]

Hi Nebu,

with your formula, if one has any list of names which are lesser than "All", then those gets 2 repeated twice instead of 2.
use below list of Examples:
Fred
Linda
Aana
Albert
Aaron
All

Aana and Aaron reflects 2!

Regards,
Prasad DN

 

[prasaddn]

Hi Krishna,

I have also entered "All" manually in first cell, but the formula will ensure rest gets sorted by replacing All's position with next item.

Regards,
Prasad DN

 

[Nebu]

Hi See attached, Guess I fixed it