Function to find 'n'th largest alphanumeric field (like "Large")

[Smibes]

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

 

[Mike H]

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

Mike

 

[Gary''s Student]

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

 

[Smibes]

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

 

[Max]

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.

 

[T. Valko]

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

 

[ShaneDevenshire]

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.

 

[T. Valko]

Neither handles duplicates.

Biff

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.

 

[ShaneDevenshire]

Absolutly correct.
--
Thanks,
Shane Devenshire

Neither handles duplicates.

Biff