The Kth element in a text list

kjs

unread,

Feb 6, 2007, 1:35:40 PM2/6/07

to

=Small(A1:A5,2) from
12
15
12
10
17
returns the value 12, but what from
def
ghi
qrs
def
abc
returns "def" as the second lowest, by sort order
or ascii value?
I can get as far as =MIN(CODE(A1:A5)) entered
as an array formula giving 97, the lowest ascii
value of the first letter, but then I come off the rails.
Any ideas?

kjs

Ron Coderre

unread,

Feb 6, 2007, 2:21:03 PM2/6/07

to

It might be able to be done.....but, it depends on what you're really
working with.
So, if your posted example does not reflect actual scenario, we need to know.

That being said....Maybe something like this?

With this list of 3-letter values in A1:A10
abc
add
def
aab
bft
dhi
wcl
zet
drw
jkl

B1: (the rank item you want....eg 5 means you want the 5th smallest)

This formula returns that ranked item from the list
C1:
=INDEX(A1:A10,MATCH(SMALL(INDEX(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A10),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0),B1),INDEX(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A10),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0),0))

or....if you prefer an ARRAY FORMULA version (committed with ctrl+shift+enter)
C1:
=INDEX(A1:A10,MATCH(SMALL(--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A10),2,1))&CODE(MID(UPPER(A1:A10),3,1))),B1),--(CODE(MID(UPPER(A1:A10),1,1))&CODE(MID(UPPER(A1:A10),2,1))&CODE(MID(UPPER(A1:A10),3,1))),0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

T. Valko

unread,

Feb 6, 2007, 2:28:32 PM2/6/07

to

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COUNTIF(rng,"<"&rng),0))

Where k = the kth element

Biff

"kjs" <1@2.3> wrote in message
news:%23EksX2h...@TK2MSFTNGP03.phx.gbl...

kjs

unread,

Feb 6, 2007, 2:37:20 PM2/6/07

to

Ron Coderre wrote...

> It might be able to be done.....but, it depends on what you're really
> working with.
> So, if your posted example does not reflect actual scenario, we need to know.

You've put your finger on the limitation of my query - the text values, and I don't
yet have specific data, would be of any length not just 3 letters.

> That being said....Maybe something like this?

> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP

It sure does - it gives me one oar when I had none.

kjs

unread,

Feb 6, 2007, 2:52:48 PM2/6/07

to

T. Valko...

> Try this:
>
> Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
> ENTER):
>
> =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COUNTIF(rng,"<"&rng),0))
>
> Where k = the kth element
>
> Biff

What a marvel! I'll spend the rest of the day figuring just how that works.
Thank you.

kjs

Ron Coderre

unread,

Feb 6, 2007, 3:00:03 PM2/6/07

to

Then you might consider this

If list order does not matter:
1) sort the list in ascending order
2) run a series of numbers (from 1 to the list count) next to the list

Then the n-th ranked numeric value corresponds to the n-th ranked text list
item.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Ron Coderre

unread,

Feb 6, 2007, 3:14:01 PM2/6/07

to

I think my formula stash is getting too big. That formula was in it, but I
completely missed it. Fortunately, you didn't!

***********
Regards,
Ron

XL2002, WinXP

T. Valko

unread,

Feb 6, 2007, 4:48:55 PM2/6/07

to

You're welcome. Thanks for the feedback!

Biff

"kjs" <1@2.3> wrote in message

news:%23lgaZhi...@TK2MSFTNGP02.phx.gbl...

T. Valko

unread,

Feb 6, 2007, 5:03:37 PM2/6/07

to

I have it stashed under: Sort text using formula

I think it was in a post by Harlan where I first saw this technique. Until
then, I had also used some variation of CODE.

Biff

"Ron Coderre" <ronREMOVET...@bigfoot.com> wrote in message
news:3D280249-2872-4EBF...@microsoft.com...

Epinn

unread,

Feb 6, 2007, 10:26:32 PM2/6/07

to

Wow! This formula is so versatile. Thanks Biff for sharing.

I had so much fun playing with it and came up with the following findings. Please correct me if I am wrong.

Keep SMALL and change "<" to ">", I get the kth element in descending order.

Keep "<" and change SMALL to LARGE, I also get the kth element in descending order.

With SMALL, "<" and k = 1, I get the smallest element.

With LARGE, "<" and k = 1, I get the largest element.

With SMALL, ">" and k = 1, I also get the largest element.

All this is good only when I have only numbers (right aligned) or only text (left aligned) in the range.

If I have text and numbers in the range, I don't trust any of the above formulae.

e.g. A1:A10 I only have zzz (text) or 999 (number).

I get the same result regardless of whether I use SMALL or LARGE in the formula. I am keeping the "<" in the formula. I don't understand why.

I also don't trust the formula when I have blanks. I only focus on all text or all numbers plus blanks in the range.

e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COUNTIF(rng,"<"&rng),0))

rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination in between. No blanks.

The formula returns aaa as the smallest element. No problem. Now delete aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the blank in A10. ???

I am missing something.

Appreciate input.

Epinn

"T. Valko" <biffi...@comcast.net> wrote in message news:eiQq#TiSHH...@TK2MSFTNGP05.phx.gbl...

Epinn

unread,

Feb 6, 2007, 11:01:37 PM2/6/07

to

One more thing I just discovered.

LARGE and ">" is exactly the same as SMALL and "<" i.e. ascending order.

Epinn

"Epinn" <som...@example.com.NO_SPAM> wrote in message news:OiIzcfmS...@TK2MSFTNGP02.phx.gbl...

T. Valko

unread,

Feb 7, 2007, 12:29:10 AM2/7/07

to

Don't get carried away with </> Large/Small! It can be confusing! <bg>

That particular formula only works with TEXT and I assumed the rng only
contained TEXT. I didn't see any numbers and/or blank/empty cells
represented in the sample.

This formula works for TEXT only and accounts for blank/empty cells (no
error trap) (array entered):

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

This formula works for both TEXT and NUMBERS and accounts for blank/empty
cells (array entered). Note: without the error trap any blank/empty cells
will be included as 0's at the end of the list:

=IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)),"")

It will sort numbers first, then the text.

Biff

"Epinn" <som...@example.com.NO_SPAM> wrote in message

news:%2353vuym...@TK2MSFTNGP02.phx.gbl...

Epinn

unread,

Feb 7, 2007, 1:13:21 AM2/7/07

to

Don't get me wrong. You gave a perfect solution for the OP's request.

Yes, I got carried away. I was "greedy" and I thought I could use that formula on numbers as well. It did seem to work when I had all numbers and no blanks.

Okay, I won't drive myself crazy with Large/Small, "<" and ">" etc.

Forgive me and I shall give everyone and myself a break hopefully soon.

Thanks for the wonderful gifts. If I understand correctly, both formulae return the smallest item. I won't worry about the nth element in a range of text and numbers.

Epinn

"T. Valko" <biffi...@comcast.net> wrote in message news:OjHkmjnS...@TK2MSFTNGP02.phx.gbl...

T. Valko

unread,

Feb 7, 2007, 2:31:42 AM2/7/07

to

>I won't worry about the nth element in a range of text and numbers.

Yeah, I can't think of a situation where you'd need to know the 10th
element, but ya never know! Basically, the formula is meant to sort the
entire range by drag copying.

If you needed to know the first or last TEXT value (alphabetically) in a
TEXT ONLY list:

First:

=LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<>"")),rng)

Or, an array formula:

=INDEX(rng,MATCH(0,IF(rng<>"",COUNTIF(rng,"<"&rng)),0))

For the last, just flip the "<" to ">".

Biff

"Epinn" <som...@example.com.NO_SPAM> wrote in message

news:OKOkV8nS...@TK2MSFTNGP04.phx.gbl...

Epinn

unread,

Feb 7, 2007, 5:28:54 PM2/7/07

to

Sorry, KJS, I didn't expect I had so much to say. I should have started my own thread. If I want to discuss on first and last value I'll definitely start my own thread. Allow me to finish off with sorting on this thread.

Biff, I am glad I said "If I understand correctly......" and you clarified. I almost miss the beauty of the formulae - *sort* by *dragging* and not just the first value. You know, I was so focused on nth element because it was how it all started.

I think finally I understand every single word in your posts i.e. error trap, blanks at the end of the list, text, sort ...... Without dragging down, I missed so much.

We use COUNTIF, < etc. because it is TEXT. Of course, we can use the same formula for numbers. But why bother, when I can just do =SMALL(C$1:C$10,ROWS($1:1)) for all numbers and no blanks.

I don't really want to understand the reasoning/difference between the two. I think I have taken in a lot within the last 24 hours and I don't want to confuse myself any more. Also, I have to go and study first and last value now. I have a bunch of formulae (e.g. LOOKUP, COUNTIF) to determine last value and I have to go and check if yours is already there. Thanks for alerting me that I can flip "<" to ">" to get a different order. When I first discovered this by myself (plus LARGE/SMALL), I was so delighted.

Thank you for your wisdom and patience, my good teacher.

Epinn

"T. Valko" <biffi...@comcast.net> wrote in message news:uWNiEooS...@TK2MSFTNGP02.phx.gbl...

T. Valko

unread,

Feb 7, 2007, 5:59:35 PM2/7/07

to

>Also, I have to go and study first and last value now.
>I have a bunch of formulae (e.g. LOOKUP, COUNTIF)
>to determine last value and I have to go and check if
>yours is already there.

I didn't mean the FIRST TEXT ENTRY or the LAST TEXT ENTRY in the range. I
meant the first or last ordered alphabetically:

Brown
Smith
Williams
Adams
Luce

Adams would be the first and Williams would be the last.

Of course, one could use the original formula I posted and just change the k
parameter but the LOOKUP formula is much simpler.

Biff

"Epinn" <som...@example.com.NO_SPAM> wrote in message

news:uvt6gdwS...@TK2MSFTNGP03.phx.gbl...