# LARGE function for unique values

#### mhenk

##### Well-known Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
ABCD
1113
2122
31.0531.05
41.0541
52
63
7
Sheet3

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

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

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).

so this will not install anything on the users computers?

This seems to work

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

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
ABCDE
11113
212 2
31.0531.051.05
41.054 1
522
633
7
Sheet3

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

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.

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

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?

Replies
1
Views
119
Replies
7
Views
514
Replies
9
Views
243
Replies
5
Views
94
Replies
11
Views
240

1,196,478
Messages
6,015,442
Members
441,895
Latest member
Zululander

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back