Sort Function Without Using VBA
 

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

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

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

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

-----Original Message-----
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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

.

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.


-----Original Message-----
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

-----Original Message-----
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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

.

.

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

-----Original Message-----
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.


-----Original Message-----
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

-----Original Message-----
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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

.

.

.

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


-----Original Message-----
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

-----Original Message-----
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.


-----Original Message-----
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

-----Original Message-----
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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

.

.

.

.

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

On Wed, 9 Feb 2005 05:40:26 -0800, "Bernd Plumhoff"
wrote:

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

Hi Myrna,

Thanks - your solution seems to be optimal now!

Regards,
Bernd

ExcelMonkey,

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

DM

"ExcelMonkey" wrote in message
...
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


-----Original Message-----
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

-----Original Message-----
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.


-----Original Message-----
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

-----Original Message-----
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.



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

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.

.

.

.

.

.