alphabet

[muppet77]

whats the best way to get a value for a particular text cell , so that it ranks it in a list of cells, based on alphabetical order?

eg if i have cells a d c b, i want to get the correct ranks:

a 1
d 4
c 3
b 2

 

[Aladin Akyurek]

...Q#1. What is the purpose of the "--" (without the quotes) at the start of Aladins formula ref. 1 on the example worksheet. I cannot find any reference to these in Excel Help.
...

The formula in A2 is an equality test, with either TRUE or False as result. The -- bit coerces such a result into Excel's numerical equivalents, that is, TRUE into 1 and FALSE into 0.

 

[P Sitaram]

As regards Q2, goto Tools | Options | Edit and uncheck the extend list formats and formulas checkbox

 

[Fergus]

Thank you both, I learn something every day.

 

[muppet77]

is it possible to change the formulae so that they still rank letters if some text is removed half way through the list
eg

apples 1
pears 3
bananas 2
raspberries 4

remove pears, gives

apples 1

bananas 2
raspberries 3

so it auto updates from the list of text??????????
this is ideally what i need help on.
cheers

 

[just_jon]

Which solution have you implemented?

 

[muppet77]

Aladin Akyurek's

not au fait with code - sicking to formula

 

[just_jon]

I don't know if this is what Aladin will come up with, so check back for the A-List solution --

in B4, copied down: =IF((A4<>"")*N($A$2),SUMPRODUCT((A4>OFFSET($A$4,0,0,$B$2,1))+0)+1-($B$2-COUNTA($A$4:$A$8)),"")

 

[muppet77]

wow! cheers jon.
that's soooooooooo cool

 

[muppet77]

one last question - whats the best way to make a cell have no contents (but contain a formula in still? (so it doesn't get counted)
putting just "" doesn't work
does this make sense?!

 

[muppet77]

basically i have a dropdown pick list that takes text from another main list.
once an item is chosen, it disappears from the pick list for the next selection, until all are chosen. i am trying to get the pick list decrese in size by one every time, and NOT contain any blank lines - thus it looksup its data from a list that auto updates itself, depending whats gone.
this list is ranked in alphabetical order after every pick so that the spaces are all kept to the bottom of the drop list, and the items at the top.
however when an item is selected the blank spaces are ranked as low values, (but are not shown as a rank). ideally the (the blanks "") must be given a high rank (not 1,2,3,4 etc) to avoid being left in the same position for the drop down list

clear as mud?