LARGE function for unique values

[mhenk]

in column A, I have a list of about 1000 numbers, however, only 9-11 of them are unique. I would like to have a simple function that returns the nth largest unique number. Here is an abbreviated example:

A1-A6
1
1
1.05
1.05
2
3


B1-B4
1
2
3
4

I would like C1-C4 to return
3
2
1.05
1

Any help? Thanks

 

[NBVC]

If you download and install teh Morefunc addin from this site http://xcell05.free.fr/english/ , you can use the Uniquevalues() function, like so:

Book1
	A	B	C	D
1	1	1	3
2	1	2	2
3	1.05	3	1.05
4	1.05	4	1
5	2
6	3
7
Sheet3

Formula in C1: =LARGE(UNIQUEVALUES($A$1:$A$6),B1) copied down

 

[mhenk]

any other possibilities? I have that installed, but I can't be sure that the clients computers do.

 

[NBVC]

any other possibilities? I have that installed, but I can't be sure that the clients computers do.

If you have the latest version installed you have the ability to embed the functions into the workbook so that your clients can use it without downloading. The option is found under Tools|Morefun. (You need to make sure to allow the addition of the menu during setup).

 

[mhenk]

so this will not install anything on the users computers?

 

[Excelenator]

This seems to work

=LARGE($A$1:$A$7,B1-1+COUNTIF($A$1:$A$7,LARGE($A$1:$A$7,B1)))

 

[NBVC]

I think it copies the UDF macros....


but here's another way without using the addin, but it requires a helper column (which can be hidden).

Book1
	A	B	C	D	E
1	1	1	1	3
2	1	2		2
3	1.05	3	1.05	1.05
4	1.05	4		1
5	2		2
6	3		3
7
Sheet3

Formula in C1: =IF(COUNTIF($A$1:A1,A1)>1,"",A1)
Formula in D1: =LARGE($C$1:$C$6,B1)

 

[Aladin Akyurek]

In C1 enter:

=MAX(A1:A6)

C2:

=MAX(IF(ISNUMBER(MATCH($A$1:$A$6,$C$1:C1,0)),"",$A$1:$A$6))

which you need to confirm with control+shift+enter (not just with enter) then copy down.

 

[mhenk]

Thanks everyone. Is there a way to extend the final method (by Aladin) to a data set that is not hard-coded?

 

[Aladin Akyurek]

Thanks everyone. Is there a way to extend the final method (by Aladin) to a data set that is not hard-coded?

You mean A1:A6 is a range that grows over time and the formula must adapt itself automatically to the changing range? If so, are you on Excel 2003?