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
- Click 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.
Download Excel *.xlsx file
Unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel.xlsx
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Extract a unique distinct list and ignore blanks
Question: How do I extract a unique distinct list from a column containing blanks? Answer: Cell range B3:B12 contains several blank […]
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Extract unique distinct values A to Z from a range and ignore blanks
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
How to create a unique distinct list based on two conditions
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
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.