LARGE formula - is there an equivalent for text data?

[JodiEHBK]

What is the equivalent formula to the LARGE formula that will sort text rather than number?

Using the formula =LARGE($B2:$H2, columns ($K2:K2)) I can sort numerical data and rewrite to the second array

Is there an equivalent formula that will enable be to do the same with text entered into cells instead of numerical value?

 

[mikerickson]

Consider text in A1:A100

=INDEX(A:A, 1000*MOD(SMALL(COUNTIF(A1:A100, "<="&A1:A100)+ROW(A1:A100)/1000, 1), 1), 1)

The red 1 can be changed to get the second smallest text in A1:A100, etc.

(This is untested, it might have to be entered with Ctrl-Shift-Enter)

 

[JodiEHBK]

Thanks Mike,

I'll experiment with this. Most appreciated.

Jodi

Consider text in A1:A100

=INDEX(A:A, 1000*MOD(SMALL(COUNTIF(A1:A100, "<="&A1:A100)+ROW(A1:A100)/1000, 1), 1), 1)

The red 1 can be changed to get the second smallest text in A1:A100, etc.

(This is untested, it might have to be entered with Ctrl-Shift-Enter)

 

[Sal Paradise]

Thanks Mike,

I'll experiment with this. Most appreciated.

Jodi

Edit: Ignore, balderdash, it be broken.

If you mean sorting alphabetically, then:

Excel 2003
	A	B	C	D	E
1	Unsorted	Sorted		Unsorted	Sorted
2	467	982		Eq	ZN
3	127	934		Pb	XE
4	639	875		Wh	WH
5	934	770		Mf	UX
6	26	649		Fc	SW
7	649	639		Cd	SO
8	54	555		Lq	RW
9	67	481		Zo	PB
10	481	474		Rw	MZ
11	555	467		Ef	ME
12	438	438		Uy	LQ
13	35	313		Xf	IB
14	982	236		Mz	FE
15	875	225		Sp	FB
16	225	127		Ce	EQ
17	474	67		Bg	EE
18	236	54		Sx	CE
19	313	35		Ff	CC
20	770	26		Ic	BF
Sheet1

Cell Formulas
Range	Formula
B2	=LARGE($A$2:$A$20,ROWS(A$1:A1))
E2	{=CHAR(ROUNDDOWN(LARGE(CODE(LEFT(UPPER($D$2:$D$20),1))+CODE(MID(UPPER($D$2:$D$20),2,1))/1000,ROWS(A$1:A1)),0))&CHAR(MOD(LARGE(CODE(LEFT(UPPER($D$2:$D$20),1))+CODE(MID(UPPER($D$2:$D$20),2,1))/1000,ROWS(A$1:A1)),1)*1000)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sal Paradise]

Trying again:

Excel 2003
	A	B	C	D	E	F
1	Unsorted	Sorted		Unsorted	First 2 letter code	Alphabetical
2	467	982		Equine	90079	Zombie
3	127	934		Plumbing	88073	Xi
4	639	875		Wharf	87072	Wharf
5	934	770		Magnificent	85078	Undulating
6	26	649		Ferrous	83080	Special
7	649	639		Cadmium	83065	Sarkozy
8	54	555		Loquatious	82087	Rwanda
9	67	481		Zombie	80076	Plumbing
10	481	474		Rwanda	77079	Molybedenum
11	555	467		Effortless	77065	Magnificent
12	438	438		Undulating	76079	Loquatious
13	35	313		Xi	73067	Ichor
14	982	236		Molybedenum	70076	Fluffy
15	875	225		Special	70069	Ferrous
16	225	127		Certify	69081	Equine
17	474	67		Background	69070	Effortless
18	236	54		Sarkozy	67069	Certify
19	313	35		Fluffy	67065	Cadmium
20	770	26		Ichor	66065	Background
Sheet1

Cell Formulas
Range	Formula
B2	=LARGE($A$2:$A$20,ROWS(A$1:A1))
E2	{=LARGE(CODE(LEFT(UPPER($D$2:$D$20),1))*1000+CODE(MID(UPPER($D$2:$D$20),2,1)),ROWS(A$1:A1))}
F2	{=INDEX($D$2:$D$20,MATCH(CHAR(ROUNDDOWN(E2/1000,0))&CHAR(MOD(E2,1000)),LEFT(UPPER($D$2:$D$20),2),0),)}
Press CTRL+SHIFT+ENTER to enter array formulas.

It will break if there are two items with the same first 2 letters, or if a word is less than 2 letters. Both can be fixed if needed, but whatever.

 

[JodiEHBK]

Thanks, Sal. This worked well

Jodi