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]

basically there are 22 items in the list, and after every selection it must be cut from the next drop down selection.

source list E1:E22
sheet name "test"

drop down is two columns of 11 cells
K1:L11

would it be easier to send as i have stuff in cols A to L???

 

[just_jon]

Give Ponsy Nob a few to respond -- he writes excellent code -- and if you're still waiting tomorrow ping me and I'll pick you up.

 

[muppet77]

and yes the "event procedure" is after every selection......i suppose.

 

[DRJ]

You still want to rank based on the word minus the first letter right?

On the sheet that this is for right click on the tab and select view code.

Paste code there:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

Results (updated whenever there is a selection change)

Book1
	A	B	C	D
1	apple	anana		4
2	banana	angerine		1
3	orange	ango		6
4	mango	pple		3
5	cucumber	pple		8
6	apple	range		5
7	Zucchini	ucchini		7
8	Tangerine	ucumber		2
Sheet1

 

[DRJ]

Make the column width for B and C = 0

 

[Legacy 21301]

I still don't understand exactly what you need.

The code I posted merely ranks the list alphabetically and puts a ranking number in an adjacent column, but you are referring to cutting items from the list.

Please explain further.
It is probably better that you find someone to whom you can send the workbook, or make it available on the web.

(I presume that DRJ misunderstood your earlier post and that you need a normal alphabetically ranking - not a ranking based on the second letter only.)

 

[just_jon]

I still don't understand exactly what you need.

The code I posted merely ranks the list alphabetically and puts a ranking number in an adjacent column, but you are referring to cutting items from the list.

Please explain further.
It is probably better that you find someone to whom you can send the workbook, or make it available on the web.

(I presume that DRJ misunderstood your earlier post and that you need a normal alphabetically ranking - not a ranking based on the second letter only.)

From page 1

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?

 

[Legacy 21301]

To just_jon

The OP's reply that you have quoted was in response to a question about what he wants to do if two or more items on the list start with the same letter.

My interpretation of his reply is that he wants hem ranked alphabetically in the normal manner.

 

[just_jon]

Yup; didn't get that part in the quote somehow -- must be those Invisible mouse mice that ate it.

 

[muppet77]

gonna try and post sheet on here.
give me a bit.