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 article

Count occurences of a specific text string in a column in excel

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

Comments(9) Filed in category: Count values, Excel

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.

Comments(2) Filed in category: Built-in features, Count values, Excel

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)

COUNTIF function

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

Comments(5) Filed in category: Excel, Functions

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.

SMALL function and LARGE function

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

Comments(12) Filed in category: Excel, Functions

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.

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

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.

Comments(14) Filed in category: Excel, Functions, Index

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 in excel

Question: How to count unique distinct values on the same date? Answer: Array formula in D3: =SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), […]

Comments(4) Filed in category: Count values, Excel, Unique distinct values

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

Comments(1) Filed in category: Count values, Dates, Excel

Sort a range by occurence using array formula in excel

Question: How do I sort a range containing multiple columns and rows by occurence? Answer: The range is sorted by […]

Comments(4) Filed in category: Excel, Sort values

Count text that occurs multiple times in excel cell

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

Comments(9) Filed in category: Count values, Excel

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

Comments(15) Filed in category: Excel, Sorted unique distinct values

Excel: Count the number of occurances an integer is 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 […]

Comments(0) Filed in category: Excel, Search/Find