Hi, this is Wayne again with a topic “Two Excel Dynamic Array Functions: UNIQUE and SORT”.
In this article, we’re going to look at two of excel’s new dynamic array, functions, the unique function and the sort function and we’ll look at how you can use the two together to do some pretty exciting things. So here i have a spreadsheet with a list of bands albums and some more information about those albums. But, as you can see, the bands are repeated and so are the albums. Why? Because it’s listed out by quarter each quarter of the year different sales results things like that.
So now, what if i want to generate just a list of all of the bands? That’S it i don’t need duplicates. I just need a list of every single band. Well, i can do that now by using the fairly new unique function in microsoft excel, and you should be able to use this function if you have the microsoft 365 version of excel. So i’m going to click here on cell j2 and then i’ll type equals unique and you’ll notice. As i start typing unique excel suggests some possible functions that i might want to use, but unique is the one i want i put in my left parenthesis now. What comes next excel is asking for an array. So what’s the array that i want excel to look for unique items in well, it’s this it’s column b.
Now i could select the whole column just by left-clicking on b, you can see what excel does it puts in b, colon b, in other words the entire b column? I should put in my right parenthesis. I don’t really have to i’ll tap, enter and you’ll notice. What it did it found every unique word or phrase in that column and reproduced it over here in cell j2. Now it couldn’t fit all of the data in j2, and so it spilled the results. Hopefully, you watched my previous video on spilled results in excel. Now these results are dynamic: we’ve spilled a dynamic array or range so now watch what happens if i change the data that that’s based upon, for example, let’s say i delete the jelly rocks they’re a really fun band. If i remove all references to them out of the band list, you’ll notice that my spilled results change to reflect that i’m going to undo that. Okay, let’s try it again, but this time i want to not only get the bands, but i also want to get the albums. Let’S try that i’ll click on j2 – and i could click on j1 doesn’t really matter. Actually, i think i’ll go with j1.
This time equals unique, left parenthesis, i’ll click and drag from b to c right parenthesis, tap, enter and look at the spilled results. It spilled both the album and the band, but only the ones that are unique. So take a look at what happens. If i insert another record, let’s say the killers, wonderful, wonderful, i tap enter, look what happened to my dynamic range or dynamic array. The spilled results change to reflect what i’ve added now you might say, but wait. The band the killers is not unique from the band. The killers – well, that’s true, but in my formula i said i wanted what’s unique in columns b and c, so it takes into account the data in b and the data in c to decide what is unique and what is not.
So because the band and album together are unique, it is listed here in my results. Okay, the next thing i’d like to try is: i would like to not only filter out the things that are not unique, but i also want to sort the results based on the band name. Let’S see if we can do that so i’ll click on j1 i’ll delete the contents of that cell and, as you know, all of this spilled data is generated based on the formula in j1.
So when i delete that it gets rid of all the spilled data, so here in j1, this time i’ll type equals unique, left parenthesis and then i’ll type in sort sort is another dynamic array, function, left parenthesis and then i’ll go over and choose b and c. Let’S take a look at my formula so far and often it’s best to look in the formula bar for this. So let’s look up there at this point, i’m going to put a right parenthesis and then another right parenthesis tap enter on the keyboard and just take a look at those results here at the right. Not only did it produce the unique albums together with the bands but they’re all alphabetized, based on the band name. Now it’s also possible to do a sort just by typing, equals sort, left parenthesis and picking a range in this case a whole column tapping enter. So you can just use the sort function to sort any range and spill the results below and sometimes to the right. So you can do it that way, but i think it’s particularly exciting to do it. The way i showed to combine sort with unique in this case, but you can also combine it with other dynamic array functions to do two things at once, to produce only the unique data and then to sort it thanks for watching this tutorial.
I hope you found it to be helpful if you did please check out my other dynamic array, function, videos and also my video on spilled results, but if you did find it to be helpful, please like follow and subscribe and when you do click the bell. So you’ll be notified when i post another video. If you’d like to support my channel, you can do that through my patreon account and by buying channel merch and you’ll, see links to those opportunities in the description below the video .