Ranking TEXT in Excel

[AryehBak]

Can someone help me with this?

Is there any way to rank TEXT in Excel. I want to automat
alphebetizing a column of text (I don't want to Data-Sort, etc). Th
function RANK seems to only work with numeric data.

Any help would be appreciated.

Thank you,
Aryeh Bak
(e-mail address removed)

 

[Don Guillett]

what does your column of text look like? post a snippet

 

[Don Guillett]

also asked in general

 

[AryehBak]

Say A1:A4 is alpha, beta, gamma, delta, I would like B1:B4 to someho
get these in alphabetical order so B1:B4 would be alpha, beta, delta
gamma.

Thanks!

Aryeh Bak
(e-mail address removed)

 

[Don Guillett]

so, why don't you want to use sort?

 

[Harlan Grove]

Say A1:A4 is alpha, beta, gamma, delta, I would like B1:B4 to somehow
get these in alphabetical order so B1:B4 would be alpha, beta, delta,
gamma.

Two ways.

Select B1:B4 and enter the array formula

=INDEX(A1:A4,MATCH(SMALL(COUNTIF(A1:A4,"<"&A1:A4),
ROW(INDIRECT("1:4"))),COUNTIF(A1:A4,"<"&A1:A4),0))

More flexibly, enter the following since cell array formula in cell B1.

=INDEX($A$1:$A$4,MATCH(SMALL(COUNTIF($A$1:$A$4,"<"&$A$1:$A$4),
ROW(B1)),COUNTIF($A$1:$A$4,"<"&$A$1:$A$4),0))

Then fill B1 down as far as needed.

 

[Harlan Grove]

so, why don't you want to use sort?

So, why don't you want to answer the OP's question?

 

[AryehBak]

It's actually for a friend of mine, so I don't know the exact reason
but he has a spreadsheet with many pages that gets updated weekly, s
he asked me.

It just seems like this should be an easy thing to do using RANK o
something like it, but I guess not.

Thanks for the help, Harlan

 

[Don Guillett]

OP did not say that another column was desired.

"alphebetizing a column of text"

to me means sort.... YMMV

 

[AryehBak]

Harlan:

Thanks again for the formula. I'm getting one very strange result an
I wonder if anyone else has experienced this before. If I copy thi
formula directly into cell B1 in the example I've described above, i
works perfectly. If, though, I F2 to get into the formula, and the
hit ENTER at the end of the line without even changing the formula a
all I then get a #N/A. If I hit ESC (prior to hitting ENTER) th
formula continues to work fine. Does this make sense to anyone?
think that this formula is exactly what I want, but obviously th
example I gave was just that, an example, and for some reason I am no
able to edit this formula.

Thanks in advance

 

[Harlan Grove]

OP did not say that another column was desired.
to me means sort.... YMMV

From the original message: "I don't want to Data-Sort".

From the OP's next follow-up: "Say A1:A4 is alpha, beta, gamma, delta, I would
like B1:B4 to somehow get these in alphabetical order so B1:B4 would be alpha,
beta, delta, gamma."

Are you sure you read what the OP posted . . . carefully?

 

[Harlan Grove]

Thanks again for the formula. I'm getting one very strange result and
I wonder if anyone else has experienced this before. If I copy this
formula directly into cell B1 in the example I've described above, it
works perfectly. If, though, I F2 to get into the formula, and then
hit ENTER at the end of the line without even changing the formula at
all I then get a #N/A. If I hit ESC (prior to hitting ENTER) the
formula continues to work fine. Does this make sense to anyone? I
think that this formula is exactly what I want, but obviously the
example I gave was just that, an example, and for some reason I am not
able to edit this formula.

It's an *ARRAY* *FORMULA*. You need to hold down [Ctrl] and [Shift] keys
*BEFORE* pressing [Enter].

 

[Don Guillett]

Harlan,
Indeed, OP did say b1:b4 in follow up.