## Create a unique distinct list and sort based on frequency

*Article updated on January 24, 2018*

### 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:**

Recommended articles

Count text string in a range (case sensitive)

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 based on value frequency

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

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- 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**

- Select cell D3
- Copy cell (Ctrl + c)
- Select cell range D4:D8
- Paste (Ctrl + v)

**Formula in E3:**

=COUNTIF($B$3:$B$14, D3)

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**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}

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

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**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.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**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.

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".

Gets a value in a specific cell range based on a row and column number.

**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 frequency the past 90 days

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 based on value frequency

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 based on frequency

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 a specific number 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 […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]### 15 Responses to “Create a unique distinct list and sort based on frequency”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

hi

how to find missing numbers range 1-49 from following line

06 11 17 33 37 47

05 06 12 14 22 34

03 20 35 41 48 49

08 12 17 24 25 45

03 10 18 22 29 45

13 16 22 23 39 43

03 10 17 31 36 45

02 24 32 37 43 49

10 16 27 28 29 42

02 35 38 39 40 48

08 23 34 36 44 47

09 16 21 26 28 29

10 15 32 33 39 41

04 11 20 33 43 45

01 08 21 22 27 36

04 12 25 36 43 49

09 12 23 26 34 43

19 23 24 30 34 46

08 17 20 21 31 45

04 06 17 27 36 48

07 08 19 28 32 41

13 17 26 30 39 47

10 15 20 29 32 43

13 19 22 25 27 45

04 09 19 21 25 42

01 12 24 37 44 47

08 19 31 34 38 42

11 14 31 33 34 47

19 22 32 33 38 48

08 13 14 20 37 40

03 08 10 13 41 47

01 02 14 16 26 30

30 34 36 37 41 49

01 02 14 17 22 37

03 09 28 34 39 47

01 07 09 15 35 37

13 14 19 24 32 33

01 09 10 24 31 41

11 14 19 23 35 41

25 26 27 31 42 49

14 16 21 26 37 48

11 22 24 34 35 40

05 10 12 13 15 17

05 12 18 22 44 45

Narend,

find missing numbers range 1-49 from each row or the whole range?

See this blog post: https://www.get-digital-help.com/2009/09/20/identify-missing-numbers-in-a-range-in-excel/

Hello,

I just stumbled over this website and it's fascinating.

Still I hope you can help me with the topic above since I get a #NUM error when using the formula. I want to extrac an unique list of data sorted by their occurance. For me it seems it works only partially.

Hope you can help. Thanks

Irina,

I have pretty much changed everyting in this post. I hope it works now.

Thanks for your comment!

How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..

Sara,

See this post: https://www.get-digital-help.com/2012/04/23/unique-distinct-records-sorted-by-frequency/

Hi Oscar, how do you exclude blanks from this? The formula to create the unique distinct list, sorted by occurrences from large to small is almost exactly what I'm looking for...I just can't seem to figure out where to wrap the IF(ISBLANK() to get this thing to exclude blanks.

Joe,

Array formula in cell D3:

See attached file:

unique-sorted-by-occurances-blanks.xls

Hi Oscar,

brilliant formulas here. Helped me out a lot.

Is it actually possible to sort list elements with an equal number of occurrence in alphabetical order?

Thanks in advance,

Gerald.

Gerald,

Great question!

See this file: unique-sorted-by-occurances-sorted-in-alphabetical-order.xls

Fantastic! Thanks a lot Oscar.

hi

i saw your all solutions about unique list but not find my answer. my question is ....i have 4 columns emp.no, name, code and program

emp no name code prog

903 a1 p30 find

903 a1 p40 generate report

903 a1 p30 find

904 a2 p40 generate reports

904 a2 p40 error

903 a1 p20 error

903 a1 p30 find

904 a2 p40 generate reports

now i want unique row with coount(means how many times row reapeting)

anju,

read this post:

Filter unique distinct row records in excel 2007

now i want unique row with coount(means how many times row reapeting)Use the countifs function to count rows:

COUNTIFS function

Hi,

How do I create a new unique distinct list from their income?

Client A -$5

Client A -$5

Client B - $1

Client C - $2

Client A - $2

Results will be:

Client A $12

Client C $2

Client B $1

This is a very good formula. At first, it was hard to understand.

With the help of debugger key F9, I was able to analyze it to gain

a better understanding.

Here is my notes using cell D5 as an example.

In cell D5, we have:

=INDEX(List, MATCH(LARGE(IF(COUNTIF($D$2:$D4, List)=0, COUNTIF(List, List),

""), 1), COUNTIF(List, List)*(COUNTIF($D$2:$D4, List)=0), 0))

To make it more readable, we substitute them with array formula:

=INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))

where

occurArray = COUNTIF(List,List)

occurArrayExLarger0 = (COUNTIF($D$2:$D4,List)=0)*occurArray

occurArrayExLargerB =IF(COUNTIF($D$2:$D4,List)=0, occurArray,"")

To debug, we highlight the individual array formula and press F9 to get their intermediate values:

COUNTIF($D$2:$D4,List) = {0;1;1;0;1;0;1;1;0;1;0;1}

COUNTIF($D$2:$D4,List)=0 (exclusion logic)

{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}

occurArray = {2;4;4;2;4;2;3;4;2;3;1;3}

occurArrayExLarger0 = {2;0;0;2;0;2;0;0;2;0;1;0}

which means to Exclude Larger occurrences (3 and 4) with 0

occurArrayExLargerB = {2;"";"";2;"";2;"";"";2;"";1;""}

which means to Exclude Larger occurrences (3 and 4) with Blank ""

=INDEX(List, MATCH(2,occurArrayExLarger0,0)))

=INDEX(List, 1) = "DD"

This will result in #NUM! for cell beyond D7 because there are only

5 unique cells. To expand dynamically, we wrap it around with ISERROR:

=IF(ISERROR(

INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0))),"",

INDEX(List,MATCH(LARGE(occurArrayExLargerB,1),occurArrayExLarger0,0)))

Now we can copy and paste it to any cell in column D.

This is the reason we need to define occurArrayExLargerB in addition to

occurArrayExLarger0 because function LARGE(all"",1) will result in #NUM!.

If it wasn't for #NUM! showing in cells beyond D7, we could simplify it to:

=INDEX(List,MATCH(LARGE(occurArrayExLarger0,1),occurArrayExLarger0,0))

without defining a new array formula occurArrayExLargerB.