Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency.
Array formula in B8:
copied down as far as necessary.
Explaining formula in cell B8
Step 1 - Count previous values
The COUNTIF function counts values based on a condition or criteria. The first argument $B$7:B7 expands when the cell is copied to cells below.
COUNTIF($B$7:B7, tbl)=0
becomes
{0,0,0,0;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; TRUE, TRUE, TRUE, TRUE}
Step 2 - Replace TRUE with frequency count
The IF function returns the corresponding frequency number if boolean value is TRUE. FALSE returns "" (nothing).
IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,$B$2:$E$5),"")
becomes
IF({TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}, {3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4},"")
and returns
{3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}.
Step 3 - Find largest value in array
The MAX function returns the maximum number in array ignoring blanks and text values.
MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))
becomes
MAX({3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4})
and returns 4.
Step 4 - Compare the largest number to array
IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
becomes
IF(4=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
becomes
IF(4={3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
becomes
IF(4={3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}, {2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, "")
and returns
{"", "", 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, "";5.33333333333333, 5.25, "", 5.16666666666667}.
Step 5 - Replace TRUE with unique number
IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")
becomes
IF(MIN({"", "", 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, "";5.33333333333333, 5.25, "", 5.16666666666667})=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")
becomes
IF(2.16666666666667=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")
becomes
IF(2.16666666666667={2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, $B$2:$E$5, "")
becomes
IF({FALSE, FALSE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE}, $B$2:$E$5, "")
becomes
IF({FALSE, FALSE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE}, {"CC", "CC", "DD", "GG";"DD", "EE", "GG", "DD";"EE", "EE", "GG", "CC";"DD", "GG", "VV", "EE"}, "")
and returns
{"","","","GG";"","","","";"","","","";"","","",""}.
Step 6 - Concatenate strings in array
The TEXTJOIN function returns values concatenated ignoring blanks in array.
TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, ""))
becomes
TEXTJOIN("", TRUE, {"","","","GG";"","","","";"","","","";"","","",""})
and returns "GG" in cell B8.
Get Excel *.xlsx 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 […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
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 […]
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
4 Responses to “Sort a range based on value 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.
Your array formulas are very interesting.
But this fails if there is two or more values whith the same frequency.
Many thanks and best regards
Thanks for your comment! I have changed the formula and the attached excel file. The formula doesn´t work with blank cells.
Hi, very nice formula! I´m trying to do something like this, but I need to show one more column at the side of each unique element with the count of occurrences :-)
Fernando,
In the above example, try this formula in C9 copied down as far as necessary.
=COUNTIF(tbl, B9) + CTRL + SHIFT + ENTER