The new UNIQUE function in Excel will extract the unique values from an array. This replaces the complicated Ctrl+Shift+Enter function in Excel. This is the fourth video in the series on Dynamic Arrays in Excel. To use the workbook from today's video: https://wwww.mrexcel.com/download-center/2018/09/unique-function-in-excel.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2236. The Unique Function in Excel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. All this week, the new modern array functions in Excel.
First, before I get to the unique function I need to rant a little bit.
The word unique to me means something very different than what it means to Microsoft in Microsoft parlance.
Home conditional formatting, highlight cells, ask for the unique values and they say that banana is the only unique value in this list because it's the only one that appears once. What good is that?
Whoever needs that?
That is an insane definition. What I call unique, is what happens after you do 'remove duplicates'.
Do, data remove duplicates will leave me just Apple, Banana and Cherry/ That's the logical definition of unique.
And now we have a problem because Microsoft has introduced a unique function and there's two definitions out there.
There's the Microsoft definition and then the rest of the world definition and so they have to pretend like they are not crazy, and so the first thing they offer us is the weird version of unique. So here is the array.
Comma. Do I want to compare by column? No, I want to compare by row. Why is that the default?
All right.
And then here we are.
Occurs only once. In other words, the products that occur only once in that entire array.
Who would ever, ever use that?
All right, and we get Quince and Kiwi appear only once.
Not useful in most cases.
=unique Here's the array. Give me a list of all the products.
Comma. I want to compare it by rows.
Comma, and then I want anything that occurs one or more times, which will give me the entire list of products, whether they were sold once or twice or three times.
How many of you think about it? Let's say your Amazon, right? Who cares about a product that was sold only once?
You want to hear about the things that were sold millions of times.
Maybe this is useful for the people who are tracking the one-hit wonders from the Billboard chart.
Yeah, they could use that. Other than that, I don't know. All right, now this is awesome.
What if we asked for two columns? I want to know a unique combination of name and product.
So =unique, here's my array, Comma. I want to compare by row and of course, I want one or more times.
I'm probably just gonna...
if I start using this all the time I'm just gonna end in comma zero, comma zero, false falses, comma zero, comma zero And we get every unique combination.
Alright, let's come back to this formula and we'll enter it okay, so these are appearing not in alphabetical sequence they're appearing in the order that they appear in the original data set. Can we send unique into the sort function?
The SORT function was covered on Tuesday. So here's the array, the sort index. Well, there's only one column so I just do ,1.
Actually, you know what? It says. It's optional. We don't even need it.
Just sort that BAM! Awesome. These new functions can be nested inside of each other.
How far will that nesting go? Filtered just the blue team? Done.
Give me just the unique names and the blue team and then sort them? Done, done, done. Look at that.
Array function inside of array function inside of array function. I never press control shift enter.
These are amazing.
Check out my new book Microsoft Excel 2019: Inside-out. Click that i on the top right hand corner.
To download the workbook from today's video, visit the URL in the YouTube description.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. All this week, the new modern array functions in Excel.
First, before I get to the unique function I need to rant a little bit.
The word unique to me means something very different than what it means to Microsoft in Microsoft parlance.
Home conditional formatting, highlight cells, ask for the unique values and they say that banana is the only unique value in this list because it's the only one that appears once. What good is that?
Whoever needs that?
That is an insane definition. What I call unique, is what happens after you do 'remove duplicates'.
Do, data remove duplicates will leave me just Apple, Banana and Cherry/ That's the logical definition of unique.
And now we have a problem because Microsoft has introduced a unique function and there's two definitions out there.
There's the Microsoft definition and then the rest of the world definition and so they have to pretend like they are not crazy, and so the first thing they offer us is the weird version of unique. So here is the array.
Comma. Do I want to compare by column? No, I want to compare by row. Why is that the default?
All right.
And then here we are.
Occurs only once. In other words, the products that occur only once in that entire array.
Who would ever, ever use that?
All right, and we get Quince and Kiwi appear only once.
Not useful in most cases.
=unique Here's the array. Give me a list of all the products.
Comma. I want to compare it by rows.
Comma, and then I want anything that occurs one or more times, which will give me the entire list of products, whether they were sold once or twice or three times.
How many of you think about it? Let's say your Amazon, right? Who cares about a product that was sold only once?
You want to hear about the things that were sold millions of times.
Maybe this is useful for the people who are tracking the one-hit wonders from the Billboard chart.
Yeah, they could use that. Other than that, I don't know. All right, now this is awesome.
What if we asked for two columns? I want to know a unique combination of name and product.
So =unique, here's my array, Comma. I want to compare by row and of course, I want one or more times.
I'm probably just gonna...
if I start using this all the time I'm just gonna end in comma zero, comma zero, false falses, comma zero, comma zero And we get every unique combination.
Alright, let's come back to this formula and we'll enter it okay, so these are appearing not in alphabetical sequence they're appearing in the order that they appear in the original data set. Can we send unique into the sort function?
The SORT function was covered on Tuesday. So here's the array, the sort index. Well, there's only one column so I just do ,1.
Actually, you know what? It says. It's optional. We don't even need it.
Just sort that BAM! Awesome. These new functions can be nested inside of each other.
How far will that nesting go? Filtered just the blue team? Done.
Give me just the unique names and the blue team and then sort them? Done, done, done. Look at that.
Array function inside of array function inside of array function. I never press control shift enter.
These are amazing.
Check out my new book Microsoft Excel 2019: Inside-out. Click that i on the top right hand corner.
To download the workbook from today's video, visit the URL in the YouTube description.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
