## Find smallest and largest unique number

This article explains how to calculate the largest and smallest number based on a condition which is if the number is unique. Unique meaning it exists only once in a column, numbers that have duplicates are not calculated.

I will also demonstrate how to calculate the k-th smallest or largest value based on the same condition using an array formula.

The image above shows a formula in cell D11, it extracts the largest unique number from column A. Cell D12 contains a formula that extracts the 100th largest unique number from column A.

#### What you will learn in this article

- Build formulas that extract the min and max number if the number itself is unique.
- Identify unique numbers in a column.
- Create a formula that returns the k-th largest and smallest number if unique

Array formula in cell D11:

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

You will find an explanation of this formula below.

Array formula in cell D12:

This array formula returns the 100th largest unqiue number from column A.

Formula in cell D15:

This formula returns the largest number from column A, note it will also return a number that has a duplicate.

Formula in cell D16:

This formula returns the smallest number from column A, note it will also return a number that has a duplicate.

Array formula in cell G11:

This array formula is dynamic meaning it returns a different value in each cell, simply copy cell G11 and paste it to cells below. The ROW function utilizes a relative cell reference that changes when the formula is copied.

### Explaining array formula in cell D11

#### Step 1 - Count the number of cells within a range that meet a given condition

The COUNTIF function counts cells that equals a specific value, the formula performs multiple calculations if we use multiple values and enter the formula as an array formula.

Note, we are using the same values in the first argument as in the second argument. The COUNTIF function returns a number larger than 1 if a value has a duplicate. COUNTIF(*range*, *criteria*)

COUNTIF(Table1[Value],Table1[Value])

returns

{1; 1; 1; 0; 1; 1; 1; 1; 1; ...}.

#### Step 2 - Check if value in array is a duplicate

The less than and larger than characters allow you to create a logical expression that returns either TRUE or FALSE.

COUNTIF(Table1[Value],Table1[Value])<> 1

becomes

{1; 1; 1; 0; 1; 1; 1; 1; 1; ...}<> 1

and returns

{FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; ...}.

#### Step 3 - Check if table value is a blank

We also need to figure out how to ignore blanks in column A, the equal sign lets you compare each value with nothing. It returns TRUE if a cell is blank.

(Table1[Value]="")

returns

{FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; ...}.

#### Step 4 - Return table value if conditions are met

The IF function lets your perform different actions based on a logical expression, in this case we want the formula to return "" (nothing) if TRUE and return the number itself if false.

IF((COUNTIF(Table1[Value],Table1[Value])< > 1)+(Table1[Value]=""),"",Table1[Value])

becomes

IF({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; ...}, "", {72; 373; 230; 0; 311; 424; 460; 418; 665})

and returns

{72; 373; 230; ""; 311; 424; 460; 418; 665; ...}.

#### Step 5 - Return max value

The MAX function simply returns the largest number in the array or cell range.

MAX(IF((COUNTIF(Table1[Value],Table1[Value])< > 1)+(Table1[Value]=""),"",Table1[Value]))

becomes

=MAX({72; 373; 230; ""; 311; 424; 460; 418; 665; ...})

and returns 694 in cell D11.

### Table category

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]

This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]

## Functions in this article

More than 1300 Excel formulas

## How to use Excel Tables

## Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form