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 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 text values, Count values, Excel

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

Sort a range by occurence using array formula in excel

Learn how to sort cell values by frequency.

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

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

Comments(0) Filed in category: 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: 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

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.

Comments(9) Filed in category: Excel

COUNTIF function

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

Comments(5) Filed in category: Excel

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

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

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: Cross reference table, Excel, INDEX function

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

Learn how to sort cell values by frequency.

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 text 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

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

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