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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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
 
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.
 
Neither handles duplicates.

Biff

ShaneDevenshire said:
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.
Click to expand...
 
Back
Top