Sort column based on frequency
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value is repeated in the list.
Table of Contents
1. Sort column based on the frequency
Question:
How do I create a new unique distinct list from a column? I also want the list sorted from large to small by the number of times a value is repeated?
Answer:
Array formula in cell D3:
1.1 How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See the picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Recommended article
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
How to copy array formula in cell D3
- Select cell D3
- Copy cell (Ctrl + c)
- Select cell range D4:D8
- Paste (Ctrl + v)
1.2 Explaining array formula in cell D3
Step 1 - Count previous items
The COUNTIF function calculates the number of cells that is equal to a condition.
COUNTIF(range, criteria)
COUNTIF($D$2:D2, B3:B14)
becomes
COUNTIF("List sorted by frequency", {"DD";"AA";"AA";"BB";"AA";"DD";"EE";"AA";"BB";"EE";"CC";"EE"})
and returns
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
Step 2 - Identify positions of previous items
The equal sign compares each value in the array to 0 (zero), the result is a boolean value TRUE or FALSE.
COUNTIF($D$2:D2, B3:B14)=0
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 3 - Count items in the list
COUNTIF(B3:B14, B3:B14)
becomes
COUNTIF({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"})
and returns
{2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}.
Step 4 - Create array containing count for new values
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), "")
becomes
IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, "")
and returns
{2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}.
Step 5 - Get largest count
The LARGE function calculates the k-th largest value from an array of numbers.
LARGE(array, k)
LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1)
becomes
LARGE({2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, 1)
and returns 4.
Step 5 - Find the position in the array of the largest count
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1), COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0)
becomes
MATCH(4, COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0)
becomes
MATCH(4, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}), 0)
and returns 2.
Step 6 - Get value based on position
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num], [area_num])
INDEX(B3:B14, MATCH(LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1), COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0))
becomes
INDEX(B3:B14, 2)
becomes
INDEX({"DD";"AA";"AA";"BB";"AA";"DD"; "EE";"VV";"BB";"EE";"VV";"EE"}, 2)
and returns "AA" in cell D3.
The following formula returns the count of the corresponding value in column D.
Formula in E3:
=COUNTIF($B$3:$B$14, D3)
Recommended articles
Counts the number of cells that meet a specific condition.
2. Sort column by frequency - Excel 365
The formula in cell D3 extracts unique distinct values from B3:B14 and returns a sorted list based on the number of instances of each value.
Excel 365 dynamic array formula in cell D3:
2.1 Explaining formula in cell D3
Step 1 - Count each value
The COUNTIF function calculates the number of cells that is equal to a condition.
COUNTIF(range, criteria)
COUNTIF(B3:B14, B3:B14)
becomes
COUNTIF({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"})
and returns
{2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}.
Step 2 - Sort values by count
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)
SORTBY(B3:B14, COUNTIF(B3:B14, B3:B14), -1)
becomes
SORTBY(B3:B14, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, -1)
becomes
SORTBY({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, -1)
and returns
{"AA"; "AA"; "AA"; "AA"; "EE"; "EE"; "EE"; "DD"; "BB"; "DD"; "BB"; "CC"}.
Step 3 - Filter unique distinct values
The UNIQUE function extracts unique distinct rows from the array.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(SORTBY(B3:B14, COUNTIF(B3:B14, B3:B14), -1))
becomes
UNIQUE({"AA"; "AA"; "AA"; "AA"; "EE"; "EE"; "EE"; "DD"; "BB"; "DD"; "BB"; "CC"})
and returns {"AA"; "EE"; "DD"; "BB"; "CC"}.
3. Get Excel example file
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
15 Responses to “Sort column based on frequency”
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.
hi
how to find missing numbers range 1-49 from following line
06 11 17 33 37 47
05 06 12 14 22 34
03 20 35 41 48 49
08 12 17 24 25 45
03 10 18 22 29 45
13 16 22 23 39 43
03 10 17 31 36 45
02 24 32 37 43 49
10 16 27 28 29 42
02 35 38 39 40 48
08 23 34 36 44 47
09 16 21 26 28 29
10 15 32 33 39 41
04 11 20 33 43 45
01 08 21 22 27 36
04 12 25 36 43 49
09 12 23 26 34 43
19 23 24 30 34 46
08 17 20 21 31 45
04 06 17 27 36 48
07 08 19 28 32 41
13 17 26 30 39 47
10 15 20 29 32 43
13 19 22 25 27 45
04 09 19 21 25 42
01 12 24 37 44 47
08 19 31 34 38 42
11 14 31 33 34 47
19 22 32 33 38 48
08 13 14 20 37 40
03 08 10 13 41 47
01 02 14 16 26 30
30 34 36 37 41 49
01 02 14 17 22 37
03 09 28 34 39 47
01 07 09 15 35 37
13 14 19 24 32 33
01 09 10 24 31 41
11 14 19 23 35 41
25 26 27 31 42 49
14 16 21 26 37 48
11 22 24 34 35 40
05 10 12 13 15 17
05 12 18 22 44 45
Narend,
find missing numbers range 1-49 from each row or the whole range?
See this blog post: https://www.get-digital-help.com/2009/09/20/identify-missing-numbers-in-a-range-in-excel/
Hello,
I just stumbled over this website and it's fascinating.
Still I hope you can help me with the topic above since I get a #NUM error when using the formula. I want to extrac an unique list of data sorted by their occurance. For me it seems it works only partially.
Hope you can help. Thanks
Irina,
I have pretty much changed everyting in this post. I hope it works now.
Thanks for your comment!
How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..
Sara,
See this post: https://www.get-digital-help.com/2012/04/23/unique-distinct-records-sorted-by-frequency/
Hi Oscar, how do you exclude blanks from this? The formula to create the unique distinct list, sorted by occurrences from large to small is almost exactly what I'm looking for...I just can't seem to figure out where to wrap the IF(ISBLANK() to get this thing to exclude blanks.
Joe,
Array formula in cell D3:
See attached file:
unique-sorted-by-occurances-blanks.xls
Hi Oscar,
brilliant formulas here. Helped me out a lot.
Is it actually possible to sort list elements with an equal number of occurrence in alphabetical order?
Thanks in advance,
Gerald.
Gerald,
Great question!
See this file: unique-sorted-by-occurances-sorted-in-alphabetical-order.xls
Fantastic! Thanks a lot Oscar.
hi
i saw your all solutions about unique list but not find my answer. my question is ....i have 4 columns emp.no, name, code and program
emp no name code prog
903 a1 p30 find
903 a1 p40 generate report
903 a1 p30 find
904 a2 p40 generate reports
904 a2 p40 error
903 a1 p20 error
903 a1 p30 find
904 a2 p40 generate reports
now i want unique row with coount(means how many times row reapeting)
anju,
read this post:
Filter unique distinct row records in excel 2007
now i want unique row with coount(means how many times row reapeting)
Use the countifs function to count rows:
COUNTIFS function
Hi,
How do I create a new unique distinct list from their income?
Client A -$5
Client A -$5
Client B - $1
Client C - $2
Client A - $2
Results will be:
Client A $12
Client C $2
Client B $1
This is a very good formula. At first, it was hard to understand.
With the help of debugger key F9, I was able to analyze it to gain
a better understanding.
Here is my notes using cell D5 as an example.
In cell D5, we have:
=INDEX(List, MATCH(LARGE(IF(COUNTIF($D$2:$D4, List)=0, COUNTIF(List, List),
""), 1), COUNTIF(List, List)*(COUNTIF($D$2:$D4, List)=0), 0))
To make it more readable, we substitute them with array formula:
=INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))
where
occurArray = COUNTIF(List,List)
occurArrayExLarger0 = (COUNTIF($D$2:$D4,List)=0)*occurArray
occurArrayExLargerB =IF(COUNTIF($D$2:$D4,List)=0, occurArray,"")
To debug, we highlight the individual array formula and press F9 to get their intermediate values:
COUNTIF($D$2:$D4,List) = {0;1;1;0;1;0;1;1;0;1;0;1}
COUNTIF($D$2:$D4,List)=0 (exclusion logic)
{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}
occurArray = {2;4;4;2;4;2;3;4;2;3;1;3}
occurArrayExLarger0 = {2;0;0;2;0;2;0;0;2;0;1;0}
which means to Exclude Larger occurrences (3 and 4) with 0
occurArrayExLargerB = {2;"";"";2;"";2;"";"";2;"";1;""}
which means to Exclude Larger occurrences (3 and 4) with Blank ""
=INDEX(List, MATCH(2,occurArrayExLarger0,0)))
=INDEX(List, 1) = "DD"
This will result in #NUM! for cell beyond D7 because there are only
5 unique cells. To expand dynamically, we wrap it around with ISERROR:
=IF(ISERROR(
INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))),"",
INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0)))
Now we can copy and paste it to any cell in column D.
This is the reason we need to define occurArrayExLargerB in addition to
occurArrayExLarger0 because function LARGE(all"",1) will result in #NUM!.
If it wasn't for #NUM! showing in cells beyond D7, we could simplify it to:
=INDEX(List,MATCH(LARGE(occurArrayExLarger0,1),occurArrayExLarger0,0))
without defining a new array formula occurArrayExLargerB.