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

 

[muppet77]

i want to rank the words on the second letter of the word (after the first), then third etc
ie alphabetical order - the title of the thread
is this poss do you think?

 

[Aladin Akyurek]

i want to rank the words on the second letter of the word (after the first), then third etc
ie alphabetical order - the title of the thread
is this poss do you think?

I must be talking Greek...

We try again: Given...

apple
apple
banana
orange
mango
cucumber
apple

what would be the values to be assigned?

 

[muppet77]

sorry!

all values are different.

no two apples
just one

 

[mdmilner]

What you describe sounds like the 'stock' sort function.
Can you just sort your range and then rank them by incrementing a counter as you move thru the first column of the range object?

For each loops tend to be perfect for this type of task.

Public Function ParseRange()
Dim C As Variant

For Each C In Worksheets("A_Sheet_Name").Range("a_range")
    If Len(C.Value) = 0 Then
        ' Found the end.
           Exit For
    Else
        '  Do something with it
    End If
Next C

End Function

Mike

 

[muppet77]

not sure what that is.

don't want to use the sort function at all.
must update itself.

 

[Aladin Akyurek]

sorry!

all values are different.

no two apples
just one

muppet77.xls
	A	B	C	D
1	AuditCheck	8
2	1	5
3	List	LexicalRank
4	apple	1
5	banana	2
6	orange	5
7	mango	4
8	cucumber	3
9
Sheet1

Formulas...

A2:

=--(COUNTA(A4:A8)=SUMPRODUCT((A4:A8<>"")/COUNTIF(A4:A8,A4:A8&"")))

B1:

=IF(N(A2),MATCH(REPT("z",255),A:A),"")

B2:

=IF(N(B1),B1-(CELL("Row",A4)-1),"")

B4:

=IF((A4<>"")*N($A$2),SUMPRODUCT((A4>OFFSET($A$4,0,0,$B$2,1))+0)+1,"")

 

[DRJ]

A VBA solution

Col A has your data
Col B, And C, are empty and can be hidden (i.e. width = 0)
Col D has rank number

Option Explicit

Sub RankIt()

Dim x               As Long
Dim LastRow         As Long

    LastRow = Range("A65536").End(xlUp).Row
    For x = 1 To LastRow
        Range("B" & x).Value = Mid(Range("A" & x).Value, 2, Len(Range("A" & x).Value))
        Range("C" & x).Value = x
    Next x
    Range("B1:C" & LastRow).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    For x = 1 To LastRow
        Range("D" & x).Value = Application.WorksheetFunction.Match(x, Range("C1:C" & LastRow), 0)
    Next x
    
End Sub

 

[mdmilner]

Have to apologize, I didn't clearly say what I meant...but, what I meant was rather elegantly done by DRJ. By the stock sort, I meant to use a custom VBA function that used the stock sorting function.

My code snippet was just a code sample to show a way to walk thru a specific range...and, I got to learn something. I'd noticed this line before, but didn't even think about what it must do (easier than what I had been using) until I saw it here.

LastRow = Range("A65536").End(xlUp).Row

Thanks

Mike

 

[muppet77]

wow!

thanks to everyone.

problem solved!!!

 

[Fergus]

I have been following this discourse with interest, modelling the various answers on a worksheet, and would like to ask the gurus a couple of supplementary questions related to the various answers given.

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.

Q#2. (maybe this should be a new thread, but here goes anyway) At the start of the questions I entered the letters a, d, b and c in to cells A1 down to A4. Then I entered the formula suggested by Sitaram =Code(A1)-64 into cell B1 and filled down to B4 and it all worked fine. When I entered another letter into the next cell down in column A, i.e. cell A5, the formula (and the result) automatically appeared in cell B5 although I had only filled down to cell B4. How can I prevent this automatic extension of a fill down?