## Sort a range based on value frequency

*Article last updated on February 16, 2018*

**Question:** How do I sort a range containing multiple columns and rows by occurence?

**Answer: **The range is sorted by occurrence from large to small. See picture below.

**Array formula in B9:**

copied down as far as necessary.

Recommended articles

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

How to count unique distinct occurrences for each date

Question: How to count unique distinct values on the same date? Answer: Array formula in D3: =SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), […]

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Unique distinct list sorted based on occurrance in a column in excel

Question: How do I create a unique distinct list from a column, sorted by occurance? Answer: Array formula in A17: […]

**Named ranges**

tbl (B3:E6)

What is named ranges?

### Download excel sample file for this tutorial

Sort a range by occurences.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true],[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**NOT(**logical**)**

Changes FALSE to TRUE or TRUE to FALSE

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROWS(**array**)** returns the number of rows in a reference or an array

**MAX(**number1,[number2],**)
**Returns the largest value in a set of values. Ignores logical values and text.

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Extract unique distinct values sorted based on sum of adjacent values

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]

Extract unique distinct values from a related table

In a previous post I described how to do lookups in a related table. In this post I am going […]

Question: how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on I […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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