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 a range by frequency.xlsx
Related articles
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
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 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 […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
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 […]
Array formula in B2: =INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)) copied down as far as needed. To enter an […]
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 […]
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