LARGE function for unique values

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
any other possibilities? I have that installed, but I can't be sure that the clients computers do.
 
Upvote 0
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).
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
Thanks everyone. Is there a way to extend the final method (by Aladin) to a data set that is not hard-coded?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,222,195
Messages
6,164,510
Members
451,900
Latest member
lamski

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?

Disable AdBlock

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
Back
Top