Find min and max unique and duplicate numerical values

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

Get Excel *.xlsx file

Duplicate values category

Unique values category

More than 1300 Excel formulas

Excel categories

Comments (4)