Excel: Combine the awesome UNIQUE and SORT functions in Excel

The UNIQUE function in Excel is one of my favorite functions. Unique returns a list of unique values in a range. The SORT function in Excel sorts the contents of a range or array. The Unique function can return Unique or Distinct. I'll explain the difference in Unique vs. Distinct in this short video along with multiple examples of combining these two incredible functions.

Chris Menard's blog post:
The file used in this video is in the blog post.

Chapters:
0:00 Intro
0:35 Unique function
1:20 Sort function
1:59 Sort and Unique
2:53 Unique not distinct
4:30 Unique and Concatenate
5:46 Sort and Unique by a field

UNIQUE function Syntax and Arguments
The Unique function has three arguments. Two are optional.

Syntax
=UNIQUE (array, [by_col], [exactly_once])

Arguments
array - Range or array from which to extract unique values.
by_col - [optional] How to compare and extract. By row = FALSE (default); by column = TRUE.
exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default).
Unique vs. Distinct
The function is called the Unique function, but technically it is finding Distinct values. What is the difference between Distinct vs. Unique?

Unique values are the items that appear only once. Distinct values are all different items in a list. So something that is Unique is Distinct, but something that is Distinct may not be Unique. See my example below.

Note:
The Unique and Sort function work with Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web. Excel for iPad, Excel for iPhone, Excel for Android tablets. Excel for Android phones.

#msexcel #microsoftexcel #chrismenardtraining #chrismenard

Chris Menard's website:

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[tradz]

Great video...really like how you are pushing the limit with using unique.

[janpantera]

Fantastic Video. Clear and to the point.

[docvigo]

great video Chris, is there a way to create a list of non-uniques ie: a list of all values that are duplicates.

[jsofrehfood]

Hi Chris, thanks for sharing this. I’ve currently got two columns e, f. - e with unique from table; f with countif sorted by largest to lowest amounts. Is there any way to sort unique by the order of countif data in f? Thank you!

[tamersalem]

How are you my dear sir, may God keep you for us, thank you for your interest in spreading science, please help me get a solution to a problem regarding the unique function that you want to use with history so that if there is a table with names and in a history and needs to make a unique function according to the chosen date thank you very very much

[ubaidillahmuhammad]

nice. May I have its file.?? put in the description

[Joda-esxd]

Hi, how can we get the total number of the unique value by name? For example Aflac In count 2.

[darylellis]

Hello - I'm looking for a way to find the maximum TEXT value within a filtered table.. However, I have learned that finding a maximun text value not allowed? I was then hoping to use the unique function within a filtered excel table but that pulls in all unque values. Any thoughts.

[LindsayDavis-sh]

I'm trying to use the =SORT(UNIQUE) function in a drop-down list on worksheet A that evaluates data in a table on worksheet B (same workbook) but keep getting the response that it evaluates to an error. It works in a cell but not when I try to do a drop-down list. I have over 50 unique values - is this the issue?

[RIPChrisNazir]

how can I use unique and sort together for time, I used the same as advised but it doesnt sort the hours, it should start from 12:00AM

[OOAK-Ireland]

sorry but this nothing more than databasing. For me excel means calculations. What if you add all the amounts from every unique company? you should have two columns: company and the number of total values. I want the new array then sorted by the total values.