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