### Question:

How do I create a new unique distinct list from a column. I also want the list sorted from large to small by the number of occurrances?

Array formula in cell D3:

=INDEX(List, MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0))

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Named ranges
List
(B3:B14)

1. Select cell range B3:B14.
2. Type List in Name Box. See picture above.

What is named ranges?

How to copy array formula in cell D3

1. Select cell D3
2. Copy cell (Ctrl + c)
3. Select cell range D4:D8
4. Paste (Ctrl + v)

Formula in E3:

=COUNTIF(List, D3)

Explaining array formula in cell D3

Step 1 - Remove duplicates

=INDEX(List, MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0))

IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), "")

becomes

IF(COUNTIF("List sorted by occurrances", {"DD";"AA";"AA";"BB";"AA"; "DD";"EE";"AA";"BB";"EE";"CC";"EE"})=0, COUNTIF({"DD";"AA";"AA";"BB";"AA"; "DD";"EE";"AA";"BB";"EE";"CC";"EE"}, {"DD";"AA";"AA";"BB";"AA"; "DD";"EE";"AA";"BB";"EE";"CC";"EE"}), "")

becomes

IF({0;0;0;0;0;0;0;0;0;0;0;0}=0, {2;4;4;2;4;2;3;4;2;3;1;3}, "")

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}, {2;4;4;2;4;2; 3;4;2;3;1;3}, "")

and returns

{2;4;4;2;4;2;3;4;2;3;1;3}

Step 2 - Find maximum value

=INDEX(List, MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0))

LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1)

becomes

LARGE({2;4;4;2;4;2;3;4;2;3;1;3}, 1)

and returns 4.

Step 3 - Return the relative position of an item in an array that matches a specified value

=INDEX(List, MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0))

MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0)

becomes

MATCH(4, COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0)

becomes

MATCH(4, {2;4;4;2;4;2;3;4;2;3;1;3}), 0)

and returns 2.

Step 4 - Return a value or reference of the cell at the intersection of a particular row and column, in a given range

=INDEX(List, MATCH(LARGE(IF(COUNTIF(\$D\$2:D2, List)=0, COUNTIF(List, List), ""), 1), COUNTIF(List, List)*(COUNTIF(\$D\$2:D2, List)=0), 0))

becomes

=INDEX(List, 2)

becomes

=INDEX({"DD";"AA";"AA";"BB";"AA";"DD"; "EE";"VV";"BB";"EE";"VV";"EE"}, 2)

and returns "AA".

unique-sorted-by-occurances.xls
(Excel 97-2003 Workbook *.xls)