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?

Answer:

Array formula in cell D3:

=INDEX($B$3:$B$14, MATCH(LARGE(IF(COUNTIF($D$2:D2, $B$3:$B$14)=0, COUNTIF($B$3:$B$14, $B$3:$B$14), ""), 1), COUNTIF($B$3:$B$14, $B$3:$B$14)*(COUNTIF($D$2:D2, $B$3:$B$14)=0), 0))

Recommended articles

Count text string in a range

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

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), ""), […]

Sort a range by occurence using array formula in excel

Learn how to sort cell values by frequency.

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: […]

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.

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

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($B$3:$B$14, D3)

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

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}

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

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.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

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.

How to use Excel’s MATCH function

Identify the position of a value in an array.

Step 4 - Return a value in a specific position 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".

INDEX function explained

Fetch a value in a data set based on coordinates.

Download excel example file

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

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), ""), […]

Calculate occurences the past 90 days in excel

Question: I have two columns of data... A1-A10 contain a list of dates. B1-B10 contain a value, AA, BB, CC, […]

Sort a range by occurence using array formula in excel

Learn how to sort cell values by frequency.

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

Create a unique distinct list and sort by occurrances from large to small

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

Count the number of times an integer exists in a list

Question: How many times is the value 3 (B2) in this list (A:A)? Answer: =COUNT(SEARCH(B2,A:A)) + CTRL+ SHIFT + ENTER […]