Sort Function Without Using VBA

[ExcelMonkey]

Is it possible to create a Sort Function for text in Excel
using Array Formuals? I want to do a Sort without
employing VBA. I can build a simple sort but it only
works off the first letter in the text string. I use the
code funtion to get code of first character of each text
item. Then I rank this item relative to entire array of
text. Lastly, I use the small formula to order the ranks
from smallest to largest. But as you can see, I get
duplicates in the Small column because I have duplicate
first letters in the rows that make up the text items.


Code Rank Small
Jim 74 4 1
Dean 68 2 2
Allen 65 1 2
Rue 82 7 4
Dave 68 2 5
Sydney 83 11 5
Mary 77 5 7
Rodney 82 7 7
Tom 84 12 7
Rob 82 7 7
Murray 77 5 11
Ron 82 7 12

I was orignally thinking that if I could calculate the Code
() number for each letter in each text string then I could
multiply them togeter like. For Example with Rob and Ron:

Code(R)*Code(o)*Code(b)= 891,996
Code(R)*Code(o)*Code(n)= 1,001,220

I could then apply the rank and small functions on this
number and get an accurate sort. I need to pull the
entire text string into a cell and apply some sort of
array formula to accomodate the mulitple code() funtions.
Is this possible

Thanks

 

[Jason Morin]

I'm confused. Why not just use Data > Sort? Doing so does
not involve VBA.

HTH
Jason
Atlanta, GA

 

[ExcelMonkey]

Yes but I want to sort on a frequent basis. Everytime my
list updates I want to sort it in another column. To do
this, I will have to either :

1) Do it mannually
2) trigger the Sort with VBA code.

I do not want to do either. I am trying to find a way to
frequently Sort without relying on manual process or VBA
code.

 

[Bernd Plumhoff]

If your text is in A1:A12, then enter into B1:
=SUMPRODUCT(--(A1>$A$1:$A$12))+COUNTIF($A$1:A1,A1)

Enter into C1:
=INDEX(A:A,MATCH(ROW(),B:B,FALSE))

Then copy down B1:C1 to B2:C12

Your sorted text will be in C1:C12.

HTH,
Bernd

 

[Jason Morin]

Well, I guess you could try telekinesis to tell Excel to
sort. Otherwise, you have 2 options - do it yourself
(manual) or program Excel to do it (VBA). There simply is
no other way.

Jason

 

[ExcelMonkey]

If I can find a way to calculate character code for an
entire text string then I should be able to do it. But I
may have to use VB(custom function) to do this.

 

[Jason Morin]

You are contradicating yourself. You said no VBA, yet
writing a custom function *is* using VBA. What you want
is worksheet_change event that will sort your data any
time there is a change to a pre-defined set of cells.

Jason

 

[ExcelMonkey]

No thats not what I mean. I can either:

1) figure out a way to do this in Excel only (using
formulas to convert words to numerical values and then
using formulas to rank and redefine order of list)
2) as you stated use a worksheet_change event to trigger
the Sort (VBA)
3) Write a function in VBA that works like CODE()but
incorporates the entire text string. Then use excel to
rank the values and create lookups to redefine their order
4) Manually so the sort myself

I just want to know if anyone knows how to calculate a
numerical value for an entire word that can be used for
sorting purposes. If the answer is yes, then I don't
think I will need VBA to do any of the sorting.

Thanks

 

[Myrna Larson]

Hi, Bernd:

Very clever approach! Based on your example, I was able to shorten the first
formula a bit, to

=COUNTIF($A:$A,"<"&A1)+COUNTIF($A$1:A1,A1)

Myrna Larson

 

[Bernd Plumhoff]

Hi Myrna,

Thanks - your solution seems to be optimal now!

Regards,
Bernd

 

[David Myle]

ExcelMonkey,

I don't have the foggiest idea about what you are trying to articulate. Do
you really need help?

DM