Retrieve the Nth element in a dataset
 

Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

"Epinn" wrote in message
...
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care
of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my
stash although I can't think of an example why one would need to retrieve
the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

I use them to retrieve the Nth element in a data set of numbers or text.

Why do things the hard way?

For numbers I can just use

=SMALL(rng,N)

This is good if there are no duplicates.

Epinn

"Epinn" wrote in message ...
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

Interesting timing. As usual, I learned something from you.

=SMALL(IF(ISNUMBER(rng),IF(MATCH(rng,rng,0)=ROW(rn g)-MIN(ROW(rng))+1,rng)),N)

I don't really need "" in the formula. FALSE is fine.

Checking for ISNUMBER is good as it takes care of blanks.

However, both formulae give #NUM! error if N is larger than the number of unique values. I won't worry about it though.

Your example makes sense. I was thinking of integers and I scratched my head.

I won't worry about Nth unique element in a data set of *text* only.

Epinn

"T. Valko" wrote in message ...
I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

"Epinn" wrote in message
...
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care
of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my
stash although I can't think of an example why one would need to retrieve
the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula Ã* ascending order

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts. The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box. A
lookup formula uses the selected specialty description and date to return the
proper amount from the table. When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties. One other benefit is it eliminates typos (by the user - not the
person updating the table) that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.



"Epinn" wrote:

Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula Ã* ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula Ã* descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula Ã* ascending order

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts. The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box. A
lookup formula uses the selected specialty description and date to return the
proper amount from the table. When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties. One other benefit is it eliminates typos (by the user - not the
person updating the table) that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.


"Epinn" wrote:

Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula Ã* ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0)) array formula Ã* descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element.



Comments welcome from all.

Retrieve the Nth element in a dataset
 

However, both formulae give #NUM! error if N is larger
than the number of unique values.

That's true. It could be trapped but you'd have to define what to trap. If N
uniques what result would you want?

It'll also error if there are no numbers in the range.

Biff

"Epinn" wrote in message
...
Interesting timing. As usual, I learned something from you.


=SMALL(IF(ISNUMBER(rng),IF(MATCH(rng,rng,0)=ROW(rn g)-MIN(ROW(rng))+1,rng)),N)

I don't really need "" in the formula. FALSE is fine.

Checking for ISNUMBER is good as it takes care of blanks.

However, both formulae give #NUM! error if N is larger than the number of
unique values. I won't worry about it though.

Your example makes sense. I was thinking of integers and I scratched my
head.

I won't worry about Nth unique element in a data set of *text* only.

Epinn

"T. Valko" wrote in message
...
I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

"Epinn" wrote in message
...
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COU NTIF(rng,"<"&rng),0))
array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care
of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula -- descending order



Don't know if I have missed anything. Biff, I will put them away in my
stash although I can't think of an example why one would need to retrieve
the Nth element.



Comments welcome from all.