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
Duplicate values category
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 […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
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 […]
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 […]
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). […]
Functions in this article
More than 600 Excel formulas
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"