# How to list unique distinct values sorted by frequency

### What's on this page

## 1. Unique distinct values sorted based on frequency (single column)

**Question:**How do I create a unique distinct list from a column sorted by occurrence?

Array formula in D3:

copied down as far as needed.

**How to create an array formula**

- Copy above array formula
- Select cell D3
- Press with left mouse button on in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter

Formula in E3:

copied down as far as needed.

### Explaining formula in cell D3

#### Step 1 - Calculate frequency of each value

The COUNTIF function counts values based on a condition, in this case, multiple conditions.

COUNTIF($B$3:$B$15, $B$3:$B$15)

becomes

COUNTIF({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"}, {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})

and returns

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}.

#### Step 2 - Prevent duplicate values

The first argument in the COUNTIF function contains an expanding cell reference. It makes sure that prior values are not taken into account again.

COUNTIF(D$2:$D2,$B$3:$B$15)<>1

becomes

COUNTIF("Unique distinct

list based on occurances", {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})<>1

becomes

{0;0;0;0;0;0;0;0;0;0;0;0;0}<>1

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 3 - Multiply arrays

COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2,$B$3:$B$15)<>1)

becomes

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}* {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{1;2;3;2;2;1;1;1;3;1;1;2;3}.

#### Step 4 - Check if largest value is equal to 0 (zero)

The IF function returns 1 if number is not equal to 0 (zero) and the largest value in array if equal to zero using the MAX function.

IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(MAX({1;2;3;2;2;1;1;1;3;1;1;2;3})=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(3=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, 3)

and returns 3.

#### Step 5 - Find position in array

The MATCH function returns the position of a value in a cell range or array.

MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

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

and returns 3.

#### Step 6 - Return value

The INDEX function returns a value based on a row number (and column number if needed).

INDEX($B$3:$B$15, MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*COUNTIF(D$2:$D2,$B$3:$B$15)<>1)=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*IF(COUNTIF(D$2:$D2, $B$3:$B$15)=1,0,1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

INDEX($B$3:$B$15, 3)

and returns "GG" in cell D3.

### Get Excel *.xlsx file

Unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel.xlsx

## 2. Unique distinct values sorted based on frequency - Excel 365

This formula works only with a single column cell range, read this article if you want to use a multi-column cell range: Extract a unique distinct list across multiple columns and rows sorted based on frequency

Excel 365 dynamic array formula in cell D3:

### Explaining formula

#### Step 1 - Extract unique distinct values

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(*array,[by_col],[exactly_once]*)

UNIQUE(B3:B15)

becomes

UNIQUE({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})

and returns

{"DD"; "EE"; "GG"; "TT"; "SS"; "YY"; "FF"; "II"; "RR"}

#### Step 2 - Count values

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(*range*, *criteria*)

COUNTIF(B3:B15,UNIQUE(B3:B15))

#### Step 3 - Sort values based on count

The SORTBY function sorts values from a cell range or array based on a corresponding cell range or array.

SORTBY(*array, by_array1, [sort_order1], [by_array2, sort_order2]*,…)

SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)

#### Step 4 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)

x - B3:B15

y - UNIQUE(x)

LET(x,B3:B15,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))

## 3. Unique distinct values sorted based on frequency (multiple columns)

This array formula is for Excel versions prior to Excel 365, it works with a source containing multiple columns contrary to the first section above. The formula in cell B8 extracts a list sorted based on frequency based on cell range $B$2:$E$5.

Array formula in B8:

Enter the array formula in cell B8 the copy cell B8 and paste to cells below as far as necessary. I highly recommend the Excel 365 formula above, it so much smaller and easier to use.

### Explaining formula in cell B8

#### Step 1 - Count previous values

The COUNTIF function counts values based on a condition or criteria. The first argument $B$7:B7 expands when the cell is copied to cells below.

COUNTIF($B$7:B7, tbl)=0

becomes

{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}=0

and returns

{TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, TRUE}

#### Step 2 - Replace TRUE with frequency count

The IF function returns the corresponding frequency number if boolean value is TRUE. FALSE returns "" (nothing).

IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,$B$2:$E$5),"")

becomes

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

and returns

{3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}.

#### Step 3 - Find largest value in array

The MAX function returns the maximum number in array ignoring blanks and text values.

MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))

becomes

MAX({3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4})

and returns 4.

#### Step 4 - Compare the largest number to array

IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")

becomes

IF(4=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")

becomes

IF(4={3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")

becomes

IF(4={3,3,4,4;4,4,4,4;4,4,4,3;4,4,1,4}, {2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, "")

and returns

{"", "", 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, "";5.33333333333333, 5.25, "", 5.16666666666667}.

#### Step 5 - Replace TRUE with unique number

IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")

becomes

IF(MIN({"", "", 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, "";5.33333333333333, 5.25, "", 5.16666666666667})=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")

becomes

IF(2.16666666666667=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, "")

becomes

IF(2.16666666666667={2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, $B$2:$E$5, "")

becomes

IF({FALSE, FALSE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE}, $B$2:$E$5, "")

becomes

IF({FALSE, FALSE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE}, {"CC", "CC", "DD", "GG";"DD", "EE", "GG", "DD";"EE", "EE", "GG", "CC";"DD", "GG", "VV", "EE"}, "")

and returns

{"","","","GG";"","","","";"","","","";"","","",""}.

#### Step 6 - Concatenate strings in array

The TEXTJOIN function returns values concatenated ignoring blanks in array.

TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, $B$2:$E$5), ""))=IF(COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), COUNTIF($B$2:$E$5, $B$2:$E$5)*(COUNTIF($B$7:B7, $B$2:$E$5)=0), "")), (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1))*1), $B$2:$E$5, ""))

becomes

TEXTJOIN("", TRUE, {"","","","GG";"","","","";"","","","";"","","",""})

and returns "GG" in cell B8.

### Get Excel *.xlsx file

Sort a range by frequency.xlsx

## 4. List unique distinct values sorted based on frequency (multiple columns) - Excel 365

This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in statistics.

The image above shows a cell range B2:E11 that contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns a list sorted based on frequency.

Dynamic array formula in cell B15:

Check out How to create a frequency table based on text values if your data is arranged in a single column.

### Explaining formula

#### Step 1 - Rearrange values to a single column

The TOCOL function rearranges values in a 2D cell range to a single column.

TOCOL(*array*, [*ignore*], [*scan_by_col*])

TOCOL(B2:E11)

returns

#### Step 2 - Filter out blanks

The FILTER function extracts values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")

returns

The empty cells are now gone in cell B13 and cells below.

#### Step 3 - Extract unique values

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(*array,[by_col],[exactly_once]*)

UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))

returns

{"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"}.

#### Step 4 - Count values

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(*range*, *criteria*)

COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")))

becomes

COUNTIF(B2:E11, {"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"})

and returns

{6; 3; 6; 4; 4; 3; 3; 5}.

#### Step 5 - Sort values based on the frequency

The SORTBY function sorts values from a cell range or array based on a corresponding cell range or array.

SORTBY(*array, by_array1, [sort_order1], [by_array2, sort_order2]*,…)

SORTBY(UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")), COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))), -1)

becomes

SORTBY({"Banana"; "Lime"; "Pineapple"; "Strawberry"; "Orange"; "Pear"; "Raspberry"; "Apple"}, {6; 3; 6; 4; 4; 3; 3; 5}, -1)

and returns

{"Banana"; "Pineapple"; "Apple"; "Strawberry"; "Orange"; "Lime"; "Pear"; "Raspberry"}.

#### Step 6 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

SORTBY(UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>"")), COUNTIF(B2:E11, UNIQUE(FILTER(TOCOL(B2:E11), TOCOL(B2:E11)<>""))), -1)

x - B2:E11

y - TOCOL(x)

z - UNIQUE(FILTER(y,y<>""))

LET(x,B2:E11,y,TOCOL(x),z,UNIQUE(FILTER(y,y<>"")),SORTBY(z, COUNTIF(x,z),-1))

### Useful resources

How to make a frequency distribution table in Excel - pivot table

How to Make a Histogram in Excel (Step-by-Step Guide)

### Frequency table category

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

### Excel categories

### 14 Responses to “How to list unique distinct values sorted by frequency”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Your array formulas are very interesting.

But this fails if there is two or more values whith the same frequency.

Many thanks and best regards

Thanks for your comment! I have changed the formula and the attached excel file. The formula doesn´t work with blank cells.

Hi, very nice formula! I´m trying to do something like this, but I need to show one more column at the side of each unique element with the count of occurrences :-)

Fernando,

In the above example, try this formula in C9 copied down as far as necessary.

=COUNTIF(tbl, B9) + CTRL + SHIFT + ENTER

How can we do that in Excel 2010? without VBA or PowerTools or PowerPivot etc. but only using worksheet formulas. I can do it in VBA. I am asking because it's very hard to figure out using only worksheet formulas. Thanks in advance. Since dates were not shown, I would like to record my request as made on 07MAY2023.

T S,

This article will probably show you how: Unique distinct values sorted based on frequency if you can rearrange your values to a single column.

Hi, the formula given is missing a pair of parentheses thus returning only UNIQUE values not UNIQUE and DISTINCT values.

But your explanation on calculation steps contains the correct formula.

Thank you for sharing this formula.

https://i.imgur.com/fgxAyKd.png

T S,

thank you for telling me. I hope I got it right now.

Hi, I took the liberty of editing the formula presented here into one that does not require CSE.

I did it because most beginners have trouble pressing that key combo.

My editition might affect performance but if it's a very large data set (column), I don't think much will be affected on most modern computers.

I think the basic principle is still the same as the one already shared here.

=IF(SUMPRODUCT(1*(COUNTIF(T$2:$T2, $B$3:$B$12)=0))=0,"",INDEX($B$3:$B$12,MATCH(AGGREGATE(14,6,COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(T$2:$T2, $B$3:$B$12)=0),1),INDEX(COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(T$2:$T2, $B$3:$B$12)=0),0),0)))

The above formula in question can be found here:https://imgur.com/a/76rvFqP

Comment Date:17MAY2023

The following formula is for extracting only Unique values:

=IFERROR(INDEX($B$3:$B$12,MATCH(1,INDEX(COUNTIF($B$3:$B$12,$B$3:$B$12)*(COUNTIF(Y$2:Y2,$B$3:$B$12)=0),0),0)),"")

Above formula in action can be found here:https://imgur.com/5KLglwU

Dated:17MAY2023

The following formula is for extraction of Duplicated values only.

=IFERROR(INDEX($B$3:$B$12,MATCH(AGGREGATE(14,6,COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),1),INDEX(COUNTIF($B$3:$B$12, $B$3:$B$12)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),0),0),MATCH(1,INDEX((COUNTIF($B$3:$B$12, $B$3:$B$12)>1)*(COUNTIF(AE$2:AE2, $B$3:$B$12)=0),0),0)>0),"")

Above formula in action can be found here:

https://imgur.com/qCSMZhG

T S,

thank you for posting your Excel formulas.

I don't have the luxury of rearranging to a single column.

So, I modified your formula at https://www.get-digital-help.com/unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel/ to become like https://imgur.com/jCvNhdR .

Further explanation can be found at https://stackoverflow.com/questions/76405604/excel-formula-to-extract-a-sorted-list-of-topn-unique-distinct-string-text-val/76405605#76405605 .

This reply was posted on 05JUN2023 though I finished my formula 5 days after you replied.

Thank you for sharing your answer in the first place.

Allow me to share your great website with others.

This site contains many helpful articles like a treasure chest in a dungeon!

I do appreciate your kind efforts.

Thanks again.

Tragic Shadow,

thank you.