# Find min and max unique and duplicate numerical values

#### Table of Contents

- How to find the largest duplicate number
- How to find the largest duplicate number - Excel 365
- How to find the smallest duplicate number
- How to find the smallest duplicate number - Excel 365
- How to find the largest unique number
- How to find the largest unique number - Excel 365
- How to find the smallest unique number
- How to find the smallest unique number - Excel 365
- Get Excel *.xlsx file

## 1. Extract the largest duplicate number

**Question:** How do I get the largest and smallest unique and duplicate value?

The image below shows you a list of numbers in column B ($B$3:$B$21).

Max duplicate value, array formula in E2:

The largest number in the list above is 19 but it is a unique number meaning it exists only once. Number 18 is the largest duplicate number, 18 is in cell B9 and B10.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### 1.1 Explaining formula in cell E2

#### Step 1 - Count the frequency of each value

TheÂ COUNTIF function counts values based on a condition or criteria, in this case, it counts all values in the cell range.

COUNTIF($B$3:$B$21,$B$3:$B$21)

becomes

COUNTIF({2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17},{2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17})

and returns

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}.

#### Step 2 - Extract duplicate numbers

TheÂ IF functionÂ returns the row number if cell is a duplicate. FALSE returns "" (nothing).

IF(COUNTIF($B$3:$B$21,$B$3:$B$21)>1,$B$3:$B$21,)

becomes

IF({1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}>,$B$3:$B$21,)

becomes

IF({1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}>,{2; 12; 9; 10; 0; 5; 18; 18; 12; 5; 12; 19; 3; 1; 8; 16; 10; 4; 17},)

and returns

{0; 12; 0; 10; 0; 5; 18; 18; 12; 5; 12; 0; 0; 0; 0; 0; 10; 0; 0}.

#### Step 3 - Return largest value

The MAX function returns the maximum value from a cell range or array.

MAX(IF(COUNTIF($B$3:$B$21,$B$3:$B$21)>1,$B$3:$B$21,))

becomes

MAX({0; 12; 0; 10; 0; 5; 18; 18; 12; 5; 12; 0; 0; 0; 0; 0; 10; 0; 0})

and returns 18 in cell E2.

## 2. Extract the largest duplicate number - Excel 365

Formula in cell D3:

### 2.1 Explaining formula

#### Step 1 - Count each item

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(*range*,Â *criteria*)

COUNTIF($B$3:$B$21, $B$3:$B$21)

returns

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}.

#### Step 2 - Check if larger than one meaning it is a duplicate

The larger than sign is a logical operator that lets you find numbers larger than a given condition. In this case, if larger than one means it is a duplicate.

COUNTIF($B$3:$B$21,$B$3:$B$21)>1

becomes

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}>1

and returns

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

#### Step 3 - Filter duplicates

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*,Â *include*, [*if_empty*])

FILTER(B3:B21, COUNTIF($B$3:$B$21, $B$3:$B$21)>1)

becomes

FILTER({2; 12; 9; ... ; 17}, {FALSE; TRUE; FALSE; ... ; FALSE})

and returns

{12; 10; 5; 18; 18; 12; 5; 12; 10}.

#### Step 4 - Get largest value

The MAX function gets the largest number in a cell range or array.

MAX(*number1*, [*number2*], ...)

MAX(FILTER(B3:B21,COUNTIF($B$3:$B$21,$B$3:$B$21)>1))

becomes

MAX({12; 10; 5; 18; 18; 12; 5; 12; 10})

and returns 18.

## 3. Extract the smallest duplicate number

Formula in cell

The smallest number in the list above is 0 but it is a unique number meaning it exists only once. Number 5 is the smallest duplicate number, 18 is in cell B8 and B12.

This formula is almost identical to the formula in section 1, only MAX function is replaced with the MIN function. See the formula explanation above in section 1.

## 4. Extract the smallest duplicate number - Excel 365

Formula in cell D3:

This formula is almost identical to the formula in section 2, only the MAX function is replaced with the MIN function. See the formula explanation above in section 2.

## 5. Extract the largest unique number

Max unique value, formula in D3:

The largest unique number in the list above is 19.

### 5.1 Explaining formula

#### Step 1 - Count values

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(*range*,Â *criteria*)

COUNTIF($B$3:$B$21, $B$3:$B$21)

becomes

COUNTIF({2; 12; 9; ... ; 17}, {2; 12; 9; ... ; 17})

and returns

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}.

#### Step 2 - Check if a number is one

A value equal to one must be a unique value, it exists only once. The equal sign is a logical operator that lets you compare values in an Excel formula.

COUNTIF($B$3:$B$21, $B$3:$B$21)=1

becomes

{1; 3; 1; 2; 1; 2; 2; 2; 3; 2; 3; 1; 1; 1; 1; 1; 2; 1; 1}=1

and returns

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

#### Step 3 - Evaluate IF function

IF(COUNTIF($B$3:$B$21, $B$3:$B$21)=1, $B$3:$B$21, )

becomes

IF({TRUE; FALSE; TRUE; ... ; TRUE}, {2; 12; 9; ... ; 17}, )

and returns

{2; 0; 9; 0; 0; 0; 0; 0; 0; 0; 0; 19; 3; 1; 8; 16; 0; 4; 17}.

#### Step 4 - Get the largest number in the array

The MAX function gets the largest number in a cell range or array.

MAX(*number1*, [*number2*], ...)

MAX(IF(COUNTIF($B$3:$B$21, $B$3:$B$21)=1, $B$3:$B$21, ))

becomes

MAX({2; 0; 9; 0; 0; 0; 0; 0; 0; 0; 0; 19; 3; 1; 8; 16; 0; 4; 17})

and returns 19.

## 6. Extract the largest unique number - Excel 365

Formula in cell D3:

### Explaining formula

#### Step 1 - Extract unique numbers in cell range B3:B21

The UNIQUE function lets you extract both unique and unique distinct values.

UNIQUE(*array, [by_col], [exactly_once]*)

UNIQUE(B3:B21, , TRUE)

becomes

UNIQUE({2; 12; 9; ... ; 17}, , TRUE)

and returns

{2; 9; 0; 19; 3; 1; 8; 16; 4; 17}

#### Step 2 - Get largest number in array

The MAX function gets the largest number in a cell range or array.

MAX(*number1*, [*number2*], ...)

MAX(UNIQUE(B3:B21, , TRUE))

becomes

MAX({2; 9; 0; 19; 3; 1; 8; 16; 4; 17})

and returns 19.

## 7. Extract the smallest unique number

Min unique value, formula in E8:

The smallest unique number in the list above is 0.

This formula is almost identical to the formula in section 5, only the MAX function is replaced with the MIN function. See the formula explanation above in section 5.

## 8. Extract the smallest unique number - Excel 365

Excel 365 dynamic array formula in cell D3:

This formula is almost identical to the formula in section 6, only the MAX function is replaced with the MIN function. See the formula explanation above in section 6.

### Get Excel *.xlsx file

### Duplicate values category

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

### Unique values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]

### Excel categories

### 4 Responses to “Find min and max unique and duplicate numerical values”

### 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

How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, but only want to find the Min and Max on the largest/top 100 non-duplicate values.

Marc,

read post:

Find max unique value from a range that have duplicate numbers and blanks

[...] Excel, Search/Lookup, Sort values, table on Nov.20, 2012. Email This article to a Friend Marc asks:How to find Min and Max numeric values in a range of cells that have duplicate numbers and blanks, [...]

error have a problem why does it give the answer "00/01/00"