All about the Unique Function

Welcome to this video about the unique function. The unique function is one of the functions that was created by Excel when they created dynamic arrays. So we're going to walk through how this function works. So I'm guessing almost all of you have encountered this scenario where, hey, I need to create a unique list of this first name. You copy the data, you come over here, you paste it, you come up to the data ribbon and you click Remove duplicates. You say, okay, my data has a header. You click ok, and you can see, all right, I have nine unique values. And then you have the names. But then somebody comes along and they add another name down to the bottom, fill or whatever, and you do the same thing over. I'm here to tell you you don't need to do it that way anymore. There's a much easier way. So what I'm going to show you is the unique function, what you're going to find in this file, which will be available on my website. I'll leave a link to download it. You can see here, this is the syntax for how unique works. You have an array, you can do it by row or column. We're going to cover the row example today. And then there's exactly once, which I'll explain here in a minute, so you can read through that when you get the file. But let's go ahead and start. So I'm going to type in the word unique here. I'm going to come over and highlight my array and get what I call the dancing ants, or as Mike Girvin calls them, and I'm going to close it. And I have a unique list. So now there's a couple things you can do. Let's say we add somebody. Let's say down here, we add Phil George to the list. Hello. We come up here and we can see Phil George has now been added, his first name in the list. So now let's go ahead and look at what the other functions are. So, right, we see we have these other two. So let's show you in the next one over here what they do. So let's highlight unique. Let's come up here, let's highlight this. We're going to hit a comma, we're going to say, okay, false returns, unique rows. So I want rows, which is the default, and then returns items that appear exactly once or returns every distinct item. So let's go ahead and see what happens. If we return items appear exactly once, we get three names because as you'll notice, they're only in the data once. They're the ones at the bottom. Everybody else is in the data multiple times. Like if I add. Let's just say I add Phil here again. Phil George. Now, I only have two that show up here. And so that's what the unique function allows us to do. Let's say we want to sort with the unique function. Well, we wrap it in a sort here, we come to the end, and by default, if we just wrap it in a sort, we get it in ascending order. Well, let's say I want it in descending order. Well, I can come here and I can tell it my sort order. So I have my array, and right here I have the sort order. So I want it to be in descending. So I'm going to say minus one. Now I get the names in descending order. All right, so that works there. So now you've seen the basics of the unique function variable, easy to use. Well, how many of you have ever needed to create a dynamic count? So you have all these first names and you want to know, okay, well, there's 73 records. How many unique records is there? Well, you could do something like this where I take a formula and I do one and divide, count the number of times the name appears. So what that would do is let's look at the name Paul. You'll notice it's 0.5 and 0.5, because one, divide a count of two is 0.5, and then if you add them up, you get one. So that's going to give you a count of the unique first names and you drag the formula down. That always took me a while to figure out. Well, now with unique, that's much easier to do. So now we're going to do what we did above. But instead of these numbers, we want to. Instead of these names, we want to return numbers, we want to return 10, and we want to return two. So how would we do that? Well, we'd come down here, we type count eight, which what counta is, it's a formula that counts your values whether they're text or numbers. So it's similar to count. And then we're just going to wrap the count in a unique formula. So I'm going to come up here once again, highlight the names, close it, and I got to close both my brackets. So we'll close the second one and I get a count of 10. All right, well, so that worked there. I have a count of 10. Let's say if I take a name out, does it update? So let's take out the name, Phil. We're going to delete Phil from our list. Now I have a count of nine. Well what if I wanted a count of first names that only appear once so I want a count of two. Well let's take the same formula. I'm just going to copy it over here and we're going to modify it. So now all I would need to do is come to the unique. All right we know we want rows so we'll go ahead and type false here and then we'll type true here and we get a count of two because we said only give us the names that appear once so that in the nutshell is the unique formula. There's a lot of neat things you can do that you can do with that when you combine it with other formulas such as filter such as Sort and counta that I showed here. I hope you enjoyed this video about unique. If you did please give it a thumbs up and subscribe to my channel. Thank you.