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.

range-sorted-by-occurence

Array formula in B9:

=INDEX(tbl, MIN(IF(MAX(COUNTIF(tbl, tbl)*NOT(COUNTIF($B$8:B8, tbl)))=COUNTIF(tbl, tbl), ROW(tbl)-MIN(ROW(tbl))+1, "")), MATCH(MAX(COUNTIF(tbl, tbl)*NOT(COUNTIF($B$8:B8, tbl))), (COUNTIF($B$8:B8, "<>"&INDEX(tbl, MIN(IF(MAX(COUNTIF(tbl, tbl)*(COUNTIF($B$8:B8, "<>"&tbl)-ROWS($B$8:B8)+1))=COUNTIF(tbl, tbl)*(COUNTIF($B$8:B8, "<>"&tbl)-ROWS($B$8:B8)+1), ROW(tbl)-MIN(ROW(tbl))+1, "")), , 1))-ROWS($B$8:B8)+1)*COUNTIF(tbl, INDEX(tbl, MIN(IF(MAX(COUNTIF(tbl, tbl)*(COUNTIF($B$8:B8, "<>"&tbl)-ROWS($B$8:B8)+1))=COUNTIF(tbl, tbl), ROW(tbl)-MIN(ROW(tbl))+1, "")), , 1)), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

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.

This blog article is one out of thirteen articles on the same subject "unique".

  • Share/Bookmark

Related posts:

  1. Sort a range from A to Z using array formula in excel
  2. Filter unique rows and sort by date using array formula in excel
  3. Filter unique values from a range using array formula in excel
  4. Sort text cells alphabetically from two columns using excel array formula
  5. Unique distinct values from multiple columns using array formula
  6. Extract duplicates from a range using excel array formula
  7. Filter duplicate rows and sort by date using array formula in excel
  8. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
  9. Filter values existing in range 1 but not in range 2 using array formula in excel
  10. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel