Question: How do I create a unique distinct list from a column, sorted by occurance?

Answer:

Array formula in A17:

INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF(A$16:$A16, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF(A$16:$A16, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF(A$16:$A16, List)=1, 0, 1), 0)) + CTRL+ SHIFT + ENTER copied down as far as needed.

Formula in B17:

=COUNTIF(List, A17) + ENTER copied down as far as needed.

Named ranges
List (A2:A20)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named ranges. If your unique list starts at F3, change A$16:$A16 in the above array formula to $F$2:F2

Download excel sample file for this tutorial.
Unique distinct list sorted based on occurrance in a column.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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

ROW(reference) returns the rownumber of a reference

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

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

More blog articles on the same subject "unique".

How to extract a unique distinct list from a column in excel
Extract a unique distinct list from two columns using excel 2007 array formula
Extract a unique distinct list from three columns in excel

Extract distinct unique sorted year and month list from a date series in excel
Create a unique distinct list from a date range in excel

Unique values from multiple columns using array formulas
Extract a unique distinct list sorted from A-Z from range in excel
Sort a range by occurence using array formula in excel
Filter unique distinct values from two ranges combined in excel 2007

Create a unique list and sort by occurrances from large to small
Unique list to be created from a column where an adjacent column has text cell values
Create unique list from column where an adjacent column meets criteria
How to create a unique distinct list where other columns meet two criteria

Unique distinct list from a column sorted A to Z using array formula in excel

Related posts:

1. Unique distinct list from a column sorted A to Z using array formula in excel
2. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
3. Create unique distinct list sorted based on text length using array formula in excel
4. Extract a unique distinct list sorted from A-Z from range in excel
5. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
6. Filter a column and create a new unique list sorted from A to Z using array formula in excel
7. Create a unique distinct alphabetically sorted list, extracted from a column in excel
8. Extract distinct unique sorted year and month list from a date series in excel
9. Create unique distinct list from column where an adjacent column meets criteria
10. How to extract a unique distinct list of a column in excel