Using the new UNIQUE dynamic array function in Excel to return a list of distinct or unique values. This episode looks at the third and second arguments in the UNIQUE function. Also - using UNIQUE inside of SORT and TEXTJOIN.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2246: Find One-Hit Wonders with UNIQUE Hey, welcome back to MrExcel netcast. I'm Bill Jelen I want to clarify something I said last week in this episode: Unique Values New Function in Excel.
The Unique function is really doing two different things.
If you're a database person, you understand these concepts of distinct and unique.
Now to me, a unique list is one of everything that occurs in this list, which is what the database people call distinct. And you can just simply specify unique and the array like that. You don't have to specify the second or third columns and you'll get what I want, which is a list of everybody who appears in that list; whether they are there one time or multiple times. And I think last week, I said, "Well, who the heck would ever want to see the items that have appeared only once? Like if you're Amazon, why do you care about a product that's only been ordered once?" All right.
But, it is possible to do that, using the third argument. So unique of this list and then for by_column, we'll skip that one. And then we want the items that occur only once, true, which is you know something called the one-hit wonders - the people that have had only one hit on the Billboard charts are known as one-hit wonder. So, if you're trying to find one-hit wonders, by all means, this version of unique will do that.
Let's talk about the second argument.
The second argument. Here I have a series of products, like smoothies or something like that and the number of ingredients that go in that; and I want to get just the unique list of ingredients. So =Unique.
But I need unique to look at this going across, across. So that's where we specify true to compare by column. And I want every item, whether it's there one time or multiple times. All right, so we get our answer there. Very nice.
I can copy and paste including the cell, which you can't do with the old array formulas. That works great. Although, really, I'm not sure we want it specified out here in multiple rows like that.
So, let's just send that Unique into the Sort. And then send that whole thing into Textjoin, separated by comma space.
Ignore empty. Yes And you'll get one a single answer with the sorted unique list of items.
Well, hey, all of these dynamic arrays are explained in this book. 60 page eBook, Excel Dynamic Arrays.
Click that "i" on the top right hand corner. I'm making this book free through the end of 2018 So if you're watching it during this year, you can download it for free. Click that "i." Today, we talked about the new unique dynamic array function that can return either distinct or unique.
If you don't specify the second or third argument, you get the list of distinct values, which is what I always want.
But if you specify true for the third argument, you get the items to occur just once or you can use the second argument to do unique across the columns. To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
The Unique function is really doing two different things.
If you're a database person, you understand these concepts of distinct and unique.
Now to me, a unique list is one of everything that occurs in this list, which is what the database people call distinct. And you can just simply specify unique and the array like that. You don't have to specify the second or third columns and you'll get what I want, which is a list of everybody who appears in that list; whether they are there one time or multiple times. And I think last week, I said, "Well, who the heck would ever want to see the items that have appeared only once? Like if you're Amazon, why do you care about a product that's only been ordered once?" All right.
But, it is possible to do that, using the third argument. So unique of this list and then for by_column, we'll skip that one. And then we want the items that occur only once, true, which is you know something called the one-hit wonders - the people that have had only one hit on the Billboard charts are known as one-hit wonder. So, if you're trying to find one-hit wonders, by all means, this version of unique will do that.
Let's talk about the second argument.
The second argument. Here I have a series of products, like smoothies or something like that and the number of ingredients that go in that; and I want to get just the unique list of ingredients. So =Unique.
But I need unique to look at this going across, across. So that's where we specify true to compare by column. And I want every item, whether it's there one time or multiple times. All right, so we get our answer there. Very nice.
I can copy and paste including the cell, which you can't do with the old array formulas. That works great. Although, really, I'm not sure we want it specified out here in multiple rows like that.
So, let's just send that Unique into the Sort. And then send that whole thing into Textjoin, separated by comma space.
Ignore empty. Yes And you'll get one a single answer with the sorted unique list of items.
Well, hey, all of these dynamic arrays are explained in this book. 60 page eBook, Excel Dynamic Arrays.
Click that "i" on the top right hand corner. I'm making this book free through the end of 2018 So if you're watching it during this year, you can download it for free. Click that "i." Today, we talked about the new unique dynamic array function that can return either distinct or unique.
If you don't specify the second or third argument, you get the list of distinct values, which is what I always want.
But if you specify true for the third argument, you get the items to occur just once or you can use the second argument to do unique across the columns. To download the workbook from today's video, visit the URL in the YouTube description.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
