Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range

welcome to exile magic trick number 759 hey if you want to download this workbook exit my trick 759 to 760 click on the link directly below the video hey this video comes straight from the mr. excel message board Richard scholar awesome poster at the mr. excel message board posted this solution here's the problem we have a list with names and there are duplicates and it's not sorted and we want to formula that will list only the unique values and sorted alright and we want this to be dynamic so as we add new names down here the list will update the first thing we're going to do is going to create a define name that will create a dynamic range so as we add any data down here our formula will be looking at the define name and pick up any new names down here define name is just a replacement for a range of cells and we want a dynamic range of cells let's do that first and then we'll deal with the sorting part of it I'm going to build my formula over here that's going to go into the define name first and we'll see how it works and then we'll paste it into the define name dialog box now we want a dynamic range and I'm going to create the range is always going to start right there and I'm going to hit the f4 key to lock it when we paste it up in the defined names dialog box it needs to be absolute and then I'm going to type a colon now I don't know how to stop that there's a colon every time you type a colon it's it assumes you want a - - a - I'm going to f4 and then go like that now right now this formula with this colon is expecting a cell reference but we're going to do a formula after the colon that will look up always the last cell reference I'm going to use the index function now index is great it's a lookup function and it can either look up a value or a cell reference right so the array now you depending on how many you want you know you could highlight the whole column if you want or all the way down I'm just going to highlight down this many you want to highlight down far further than you'll ever have names and then I'm going to hit the f4 key so for this example I just did it down to a 20 all right that's the array we're going to look up the last value here comma and now the row number we want to find the row number of the last item so I'm going to use match match is great it can find the ordinal position or relative position of an item in the list and since we want we have words here and we want to look up the last possible value I want to put in something to look up for the lookup value like zzzzz zzzzz some the last possible word that could ever exist I'm actually going to use the repeat function I'm going to say repeat what in quotes Z comma and then the max characters you can have is 255 I mean that's kind of overkill you could just put in quotes zzzzz because there's no word that has Z zzzzzz but that'll work that'll cover all cases so we're going to look up Z Z Z excuse me little secure within that same range right there copy and control-v close parentheses the the type we want is this one greater than but if you leave it out by default it assumes it's set so I'm going to close parentheses on that and then that'll give us our row number let's just check it out go like this and f9 to evaluate it sure enough seven that's it starts out it goes down to seven Qin is the seventh item in this particular range right here control Z close parenthesis now let's highlight this and see now index if I highlight and hit f9 it looks up chin because index can look up a value ctrl Z or since it's now in the context of a colon which means it should be looking up a cell reference f9 it'll look up all the items ctrl Z I'm going to enter this with can enter and now I'm going to just test it down here I like this and hit the f9 oh ok I can see that the dynamic range is working ctrl Z or escape I'm going to get right now now I'm going to copy this control C escape the keyboard shortcut to open up name manager or defy names is ctrl f3 on the formulas ribbon you can get two names control f3 I'm going to say I already have one that's on the answer sheet right here but I'm going to create a new one and I'm going to do something easy just like a NN so I can type it quick because I'm going to have to type it alot type something short or give it an explicit name like list of names I'm going to come down here there it is I'm going to click OK and I'm going to immediately test it this this collapse button Boop I can see sure enough it's looking at the right range uncollapse click o close now I can create my formula with that n N which stands for this defined range but before we do that we want to see talk about comparative operators in terms of words now we all know comparative operators there's a 2 and a 3 equals is this 1 less than this one well we know the answer is false because it's looking at we always think of this as a comparative operator from numbers right oh I'm sorry it's true because two it's less than 3 right but what about 4 words actually let me leave that there control-z let's just say equals hey is we less than this now what's so cool about a comparative operator for words is it will look at the whole word you can see WI I WI H well H comes before I so I is further into the alphabet so this is true because this word is greater I did it wrong again I'm thinking backwards this is is this less than the answer is false because I is further through the alphabet than H now let's copy it down one of course it's going to be false here because we built a formula that says less than these are equal so of course it's false finally we get down to here and now we can see I is a is further through the alphabet so I is less now let's do the second letter right we're looking at WI I WH I well this is false because I is further through the alphabet H is not as far through the alphabet copy it down here now we get a true because I is less than J and finally we can convince ourselves it's still looking at the first letters also so that's kind of a cool thing to understand that the comparative operators work with words and letters also all right now we're going to use that very important idea of comparing words to words and seeing which ones are less than or greater than in our case lesson but first we're going to build the first part of the formula which is really a lookup we somehow have to look up the values here and we're going to use the index function well the array part of indexes hey what do you where all the value so that's easy that's our n n comma all right the row number now somehow we're going to need four and then one and then two right and then the rest of them one two three so we're going to need four first one two and then three how in the world are we going to get index you know usually we use match to look up an ordinal position but these seem to be all mixed up well we are going to use match because match will look up the ordinal or relative position of items in a list and since ultimately we're going to be comparing these and saying which one of these words are less than the other we want chin as the first one and let's think about this is chin less than any of these other ones no there are zero if we were counting there's zero items less less than chin because chin is the first one in a sorted list so our lookup value is going to be zero with that idea in mind right because there's zero Oh items less than chin our first one comma the lookup ray is going to be the tricky part but we're going to use this idea of comparing words and when you use the count to F because remember just a moment ago we said how many are less than chin there were zero well the range we're going to look up as our nn the range that's the range with items that we're going to count comma and what's the criteria what we're going to use this less than and we have to put it in double quotes and then ampersand 2n n now what does this do criteria usually put a single criteria in there but right now if I highlight this and hit the f9 key what does it do it puts a bunch of criteria in there right and so how many here's that chin right how many are less than chin it'll be zero zero and a zero will show up when we evaluate the entire count if so let's do that ctrl Z just to see how this part of it's working f9 sure enough a zero a zero and two why does the to show up for Jo and then another two right there so Joe and Joe oh because there's two items less than Joe and it that is exactly chin and chin for the second one Joe how many are less than Joe one two three four all right well that this zero looking up in this array would work but only for the first one so we're actually going to have to change this array as we copy down right now we want that zero but the next time when it goes down one row we need this two to turn to a zero control-z well how are we going to do that we're going to subtract the sum of count if and for the second count if our range well it's going to be that NN again comma and the criteria is going to be well we need an expandable range here and actually this expandable range for the criteria is actually going to have to look at whatever previous items index has delivered to the cell so I'm going to click right here and then : close parenthesis and then I'm going to put my cursor there an f4 to lock it and I'm going to close parentheses on the sum now this is kind of the hardest part to understand and also to show in this video because it's relying on the results from that this index or deliver so let's go ahead and look at one aspect here and then enter it and go down and maybe when we evaluate it one row down we'll understand it better but let's just think about this how many you unique sorted lists are in this list right here well they're 0 so let's just highlight this and hit f9 0 control-z well here is that array right here f9 control-z well if we subtract 0 from all these numbers we're going to get the same exact numbers but when we go down one we're going to need this sum count if all blah blah to evaluate to two so when we subtract 2 from all of these this will be 0 this will be 0 on all the other ones will be some number whether positive or negative now I'm going to control Z and just show you here f9 the array will stay the same because this count the sum and count if has given us a zero right now f9 all right control Z when we copy it down one more it may make a little bit more sense now we do have to put exact match because we do have duplicates in our list here so close parentheses on that close parentheses well that's going to dilute this this match construction is going to deliver the row number close parenthesis this is an array this count if right here we gave it an array for the criteria so I'm going to ctrl shift and enter when you hold ctrl shift and tap enter you're telling Excel I am putting an array formula in and those curly brackets get put in automatically that's Excel telling you hey I just did an array for you now I'm going to copy it down we're going to get some na na s and we'll do with that in just a second but let's go see and evaluate this the first thing I want to look at it this some countif right count if it counted how many of these are on the list zero how many Chin's are there in this list - so the sum added the zero and the two so that's how we got it - and let's just go ahead and look at this and hit f9 sure enough it got a to control Z and when I evaluate the count if - that sum f9 no way that's exactly as we hypothesize two minus two is zero and so the match will find that zero which is related to that Jo control Z escape let's just look at it down here because now this one a more explicitly show that we should get some counts for chin and Jo here so the count if notice the expandable range is now looking through all of these well how many Chin's and Jose are there if we just do the count if and not the sum part it's going to deliver an array see that 0 - 2 y because there's 2 Jose and two Chin's and then there's 0 of these notice that the range is delivering 3 because it's this is the criteria that's counting through this list and when you add these together it's four now I'm going to control Z you never want to evaluate two successive times in a row in edit mode because control Z doesn't work f9 there it is for control Z and so when I subtract let's just go like this f9 there's that 4 so now we're interested in the Jo in this particular row right here so this 4 minus the four from here gives us our zero there let's control Z and highlight this whole thing right here f9 and sure enough now we got a zero so now the idea that we're changing that original array as we go down to always get zeros is why the lookup value can be zero there control Z control shift enter one last thing we want to get rid of that na I'm in Excel 2010 this also works in 2007 if error I just love this it's worth it just for this fun so you don't have to repeat this twice in earlier versions 2003 and earlier you had to repeat it twice I will show you that formula in just a moment but this is great you just slap the the value there the value if that the what do you want in the cell if that big formula comes out to be an error double quote close parenthesis ctrl shift enter double click and send it down I'm going to copy it all the way down and we need to test this not only the formula will put some duplicates but the dynamic nature of this by the way if you come over here oh oh on the answer sheet I have a formula over here that works in 2003 and earlier you can see you have to list it you have to go is n/a that for the first part of the if and then you have to list a blank for true and that index whole thing again so you actually have to list it twice and it has to calculate twice alright let's test this this is so exciting Joe John John oh look at that did hit sort it perfectly and it picked up the expandable range Joe John that is just amazing man is it cool hanging out at the mr. excel message board you can go read the post there alright see you next trick

Channel: ExcelIsFun

Views: 127,022

Rating: 4.9463601 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Sorted, Unique, Remove, Duplicates, List, Of, Words, With, Formula, using, INDEX, MATCH, COUNTIF, REPT, Function, and, Comparative, Operators, function, Defined, Names, to, create, dynamic, range, Richard, Schollar, at, Mr, Message, Board, Posted:

Id: IZLAzIYfMDU

Length: 17min 1sec (1021 seconds)

Published: Fri Dec 31 2010