Question: How do I create a new unique distinct list originating from a cell range. (See picture to the right.) I also unique-sorted-occurrance1want the list sorted from large to small by the number of occurrances?

Answer: Here is the array formula in C2:C13. See picture below. =INDEX(List, MATCH(LARGE(IF(MATCH(List, List, 0)=ROW()-ROW(Unique_start)+1, COUNTIF(List, List)+ROW()/1048576, ""), ROW()-ROW(Unique_start)+1), IF(MATCH(List, List, 0)=ROW()-ROW(Unique_start)+1, COUNTIF(List, List)+ROW()/1048576, ""), 0)) + Ctrl + Shift + Enter

Formula in D2: =IF(ISTEXT(C2), COUNTIF(List, C2), "") copied down to D13. See picture below.

Named ranges
List
(A2:A13)
Unique_start
(C2)

What is named ranges?

unique-sorted-occurrance

Download excel example file.
unique-sorted-by-occurances.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

ROW(reference) Returns the rownumber of a reference

LARGE(array,k) returns the k-th largest row number in this data set

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

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

  • Share/Bookmark

Related posts:

  1. How to create a unique distinct list where other columns meet two criteria
  2. Create a unique distinct list from a date range in excel
  3. Create and sort distinct list by adjacent cell value size
  4. Unique distinct list sorted based on occurrance in a column in excel
  5. Extract a unique distinct list sorted from A-Z from range in excel
  6. Unique distinct list from a column sorted A to Z using array formula in excel
  7. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  8. Extract a unique distinct list from three columns in excel
  9. Create unique distinct list from column where an adjacent column meets criteria
  10. Create unique list from two columns