Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work with
text.

Thanks in advance.
John

It would be helpful to see the data and your expected result after sorting.

Mike

"Smibes" wrote:

Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work with
text.

Thanks in advance.
John

Let's say in col A we have:

ralph
nigel
ziggy
mark
edward
bob
apple
alone
agravate
abalone

In B1 enter:

=COUNTIF($A$1:$A$10,""&A1)+1 and copy down

we see:

ralph 2
nigel 3
ziggy 1
mark 4
edward 5
bob 6
apple 7
alone 8
agravate 9
abalone 10

col B is "pseudo-large" the biggest value corresponds to the first in
alphabetic order.

Finally in C1 enter:

=INDEX(A:A,MATCH(11-ROW(),B$1:B$10,0)) and copy down. This is the lookup
part.

and we see:

ralph 2 abalone
nigel 3 agravate
ziggy 1 alone
mark 4 apple
edward 5 bob
bob 6 edward
apple 7 mark
alone 8 nigel
agravate 9 ralph
abalone 10 ziggy

--
Gary''s Student - gsnu200730

Hi Mike,

The data would look something like this:

Column A Column B
1 Process Change
2 Technology People
3 Policy Policy
4 People Process
5 Change Technology

Column B would be derived by formula. If column A was numbers this would be
easy using the "LARGE" function =LARGE(A1:A5,n) in each cell in column B
(n = 1 to 5).

I hope this makes sense.

Cheers,
John

"Mike H" wrote:

It would be helpful to see the data and your expected result after sorting.

Mike

"Smibes" wrote:

Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work with
text.

Thanks in advance.
John

One venture using non-array formulas ..

Source data assumed in A1 down

In B1:
=IF(A1="","",IF(LEN(A1)1,CODE(LEFT(A1))+CODE(MID( A1,2,1))/10^10,CODE(LEFT(A1))))

In C1:
=IF(ROW()COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B, ROW()),B:B,0)))
Select B1:C1, copy down to cover the max expected extent of source data.
Hide away col B. Col C returns the required auto-ascending sort.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Smibes" wrote:
Hi Mike,

The data would look something like this:

Column A Column B
1 Process Change
2 Technology People
3 Policy Policy
4 People Process
5 Change Technology

Column B would be derived by formula. If column A was numbers this would be
easy using the "LARGE" function =LARGE(A1:A5,n) in each cell in column B
(n = 1 to 5).

I hope this makes sense.

Cheers,
John

Try this array formula** (does not account for empty cells):

For an ascending sort:

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),ROWS($ 1:1)),COUNTIF(rng,"<"&rng),0))

For a descending sort simply change each instance of "<" with "".

Biff

"Smibes" wrote in message
...
Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work
with
text.

Thanks in advance.
John

Hi,

Here are two short solutions, both entered as arrays:

=OFFSET(A$1,MATCH(ROW(A1),COUNTIF(D,"<="&D),),)
=INDEX(D,MATCH(ROW(A1),COUNTIF(D,"<="&D),))

The first assumes A$1 is the cell directly above the data D, where your
names are. Array formulas are entered using Shift Ctrl Enter instead of
Enter. I have named the range where the names are as D, if you don't, then
remember to make the range absolute or at least the rows of the range.
--
Cheers,
Shane Devenshire


"Smibes" wrote:

Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work with
text.

Thanks in advance.
John

Neither handles duplicates.

Biff

"ShaneDevenshire" wrote in
message ...
Hi,

Here are two short solutions, both entered as arrays:

=OFFSET(A$1,MATCH(ROW(A1),COUNTIF(D,"<="&D),),)
=INDEX(D,MATCH(ROW(A1),COUNTIF(D,"<="&D),))

The first assumes A$1 is the cell directly above the data D, where your
names are. Array formulas are entered using Shift Ctrl Enter instead of
Enter. I have named the range where the names are as D, if you don't,
then
remember to make the range absolute or at least the rows of the range.
--
Cheers,
Shane Devenshire


"Smibes" wrote:

Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work
with
text.

Thanks in advance.
John

Absolutly correct.
--
Thanks,
Shane Devenshire


"T. Valko" wrote:

Neither handles duplicates.

Biff

"ShaneDevenshire" wrote in
message ...
Hi,

Here are two short solutions, both entered as arrays:

=OFFSET(A$1,MATCH(ROW(A1),COUNTIF(D,"<="&D),),)
=INDEX(D,MATCH(ROW(A1),COUNTIF(D,"<="&D),))

The first assumes A$1 is the cell directly above the data D, where your
names are. Array formulas are entered using Shift Ctrl Enter instead of
Enter. I have named the range where the names are as D, if you don't,
then
remember to make the range absolute or at least the rows of the range.
--
Cheers,
Shane Devenshire


"Smibes" wrote:

Is there a formula function that will find 'n'th largest (or smallest)
alphanumeric field (like "Large" does with numerics).

I am trying to use formulas to move a column of alphnumeric data from one
column to another with the new column sorted alphanumerically. To move a
column of values I would use "Large" however that function does not work
with
text.

Thanks in advance.
John