Excel nth smallest DISTINCT value

excel second largest value duplicates
excel find second highest value and return adjacent cell
excel unique values
find unique values in excel multiple columns
excel find nth unique value
excel nth largest value
index match smallest value
dynamic list of unique values in excel

I have a set of values, the Small function gives me the ability to get the top 5 smallest values. but it doesnt take into account duplicates. I want to only see each value once. for example:

1 2 2 3 4 5

i want to output 1,2,3,4,5 not 1,2,2,3,4

I am putting the output into 5 different columns with the formula Small(A1:A20,[1-5]) but im not sure how to tell it to only look at each distinct value in the range

If one has access to the dynamic array formulas (currently only available to office 365 insiders) one can just put this in the first cell and the results will spill across:

=SMALL(UNIQUE(A:A),SEQUENCE(,5))


Other wise we need to use some array formula in a specific manner.

We must have something besides a number in the cell directly preceding where we put the formula in the first cell. So if I am putting the formula in C1, B1 must not contain one of the numbers as we need to refer to it.

Put this in C1:

=SMALL(IF(COUNTIF($B$1:B$1,$A$1:$A$20)=0,$A$1:$A$20),1)

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode. Then copy over 5 columns.


If one cannot leave the cell B1 without a number then we must get the array another way:

Put this array formula in the first cell:

=SMALL(INDEX($A:$A,N(IF({1},MODE.MULT(IF(MATCH($A$1:$A$20,$A:$A,0)=ROW($A$1:$A$20),ROW($A$1:$A$20)*{1,1}))))),COLUMN(A:A))

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode. Then copy over 5 columns.

Excel formula: nth largest value with duplicates, To get the nth largest value in a data set with duplicates, you can use an array formula based on the MAX and IF functions. Note: the LARGE function will easily return nth values, but LARGE will return Excel formula: nth smallest value. If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula.. Find the nth Smallest Value You can use the SMALL function to get the 1st, 2nd, 3rd, or nth smallest value in an array or range.

Here is another option and in single formula

Assume your data 1 2 2 3 4 5 put in A1:A6

B1, left blank

In C1, formula copied cross right until blank

=IFERROR(1/(1/AGGREGATE(15,6,$A$1:$A$20/($A$1:$A$20>B1),1)),"")

Edit : AGGREGATE is a function for Excel 2010 or above

Excel nth smallest DISTINCT value, If one has access to the dynamic array formulas (currently only available to office 365 insiders) one can just put this in the first cell and the  To get the nth largest value in a data set with duplicates, you can use an array formula based on the MAX and IF functions. Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data.

Here is another option:

Formula in C1:

=SMALL($A:$A,COUNTIF($A:$A,"<="&B1)+1)

Drag right...

Output the nth smallest UNIQUE number with one condition , How do I output the nth smallest unique value if column A is "Apples"? <b>​Excel 2013</b><table cellpadding="2.5px" rules="all" style="  In Excel worksheet, we can get the largest, the second largest or nth largest value by applying the Large function. But, if there are duplicate values in the list, this function will not skip the duplicates when extracting the nth largest value.

How to get the second lowest unique value if more than 1 value , Say we have data in column A like: enter image description here. In some cell, say D1 enter the array formula: =MIN(IF($A$1:$A$20>0  To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, from a set of data, you can use the SMALL function. In the example shown, the formula in G5 is: = SMALL ( times , F5 ) How this formula works The SMALL function is

Get nth Largest Value with Duplicates, find and get the Nth largest unique value in a range of cells in Excel. use the SMALL function to get the 1st, 2nd, 3rd, or nth smallest value  How to get distinct values in Excel (unique + 1 st duplicate occurrences) As you may have already guessed from the heading of this section, distinct values in Excel are all different values in a list, i.e. unique values and first instances of duplicate values. For example: To get a distinct list in Excel, use the following formulas.

Returning second smallest unique value, Hello, I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value. For example if my  Returns the k-th largest value in a data set ROW function: Returns the row number of a reference LEN function: Returns the number of characters in a text string INDEX function: Uses an index to choose a value from a reference or array SMALL function: Returns the k-th smallest value in a data set IF function: Specifies a logical test to perform

Comments
  • Upvoted for the nice dynamic formula (I guess you are an insider since recently?).
  • Yes, finally figured out how to get on the list.
  • I got this to work in excel but for some reason had problems getting it to work in my google spreadsheet... Good Answer though.
  • I had a problem getting this to work in my excel but I did get it to work in google sheets. Thats where i want to use this so Good Job :)
  • Strange this didn't work in Excel, however do note that Google Sheets makes use of dynamic arrays which makes that @ScottCraner his answer might be applicable (maybe some slight adjustments?)
  • You formula even adding IFERROR, when you copied the C1 formula across right to S1, you will find 12345 will occur again in I1:M1, O1:S1…
  • @bosco_yip, yes it would, I do not see the problem with that however since the formula will skip a cell. Good to point out though.