Unique distinct values sorted based on frequency
What's on this page
1. Unique distinct values sorted based on frequency
Array formula in D3:
copied down as far as needed.
How to create an array formula
- Copy above array formula
- Select cell D3
- Press with left mouse button on in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
Formula in E3:
copied down as far as needed.
Explaining formula in cell D3
Step 1 - Calculate frequency of each value
The COUNTIF function counts values based on a condition, in this case, multiple conditions.
COUNTIF($B$3:$B$15, $B$3:$B$15)
becomes
COUNTIF({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"}, {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})
and returns
{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}.
Step 2 - Prevent duplicate values
The first argument in the COUNTIF function contains an expanding cell reference. It makes sure that prior values are not taken into account again.
COUNTIF(D$2:$D2,$B$3:$B$15)<>1
becomes
COUNTIF("Unique distinct
list based on occurances", {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})<>1
becomes
{0;0;0;0;0;0;0;0;0;0;0;0;0}<>1
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 3 - Multiply arrays
COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2,$B$3:$B$15)<>1)
becomes
{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}* {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1;2;3;2;2;1;1;1;3;1;1;2;3}.
Step 4 - Check if largest value is equal to 0 (zero)
The IF function returns 1 if number is not equal to 0 (zero) and the largest value in array if equal to zero using the MAX function.
IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))
becomes
IF(MAX({1;2;3;2;2;1;1;1;3;1;1;2;3})=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))
becomes
IF(3=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))
becomes
IF(FALSE, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))
becomes
IF(FALSE, 1, 3)
and returns 3.
Step 5 - Find position in array
The MATCH function returns the position of a value in a cell range or array.
MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))
becomes
MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)
becomes
MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)
becomes
MATCH(3, {1;2;3;2;2;1;1;1;3;1;1;2;3}, 0)
and returns 3.
Step 6 - Return value
The INDEX function returns a value based on a row number (and column number if needed).
INDEX($B$3:$B$15, MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*COUNTIF(D$2:$D2,$B$3:$B$15)<>1)=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*IF(COUNTIF(D$2:$D2, $B$3:$B$15)=1,0,1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))
becomes
INDEX($B$3:$B$15, 3)
and returns "GG" in cell D3.
Get Excel *.xlsx file
Unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel.xlsx
2. Unique distinct values sorted based on frequency - Excel 365
This formula works only with a single column cell range, read this article if you want to use a multi-column cell range: Extract a unique distinct list across multiple columns and rows sorted based on frequency
Excel 365 dynamic array formula in cell D3:
Explaining formula
Step 1 - Extract unique distinct values
The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(B3:B15)
becomes
UNIQUE({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})
and returns
{"DD"; "EE"; "GG"; "TT"; "SS"; "YY"; "FF"; "II"; "RR"}
Step 2 - Count values
The COUNTIF function calculates the number of cells that meet a given condition.
COUNTIF(range, criteria)
COUNTIF(B3:B15,UNIQUE(B3:B15))
Step 3 - Sort values based on count
The SORTBY function sorts values from a cell range or array based on a corresponding cell range or array.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)
Step 4 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)
x - B3:B15
y - UNIQUE(x)
LET(x,B3:B15,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]
This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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.