# Find min and max unique and duplicate numerical values

#### Table of Contents

- Extract largest duplicate number
- Extract largest duplicate number - Excel 365
- Extract smallest duplicate number
- Extract smallest duplicate number - Excel 365
- Extract largest unique number
- Extract largest unique number - Excel 365
- Extract smallest unique number
- Extract smallest unique number - Excel 365

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

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

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

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

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:

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

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

=MIN(UNIQUE(B3:B21,,TRUE))

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

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

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]

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

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

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 […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]

This blog post describes how to create a list of unique words from a cell range. Unique words are all […]

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

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

### Leave a Reply to Marc

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