Ctrl + Shift + Enter: Excel Array Formulas 20: Extract Unique Lists & Sorting Formulas

welcome to video number 24 control shift enter mastering Excel or a formulas hey we're in the workbook array formula DVD book start on the sheet topics Wow do we have an amazing video here this is going to put it all together extracting concepts from video 16 unique count concepts from video number 18 and even the mult we learned last video number 19 we're going to see how to extract unique lists and look at sorting formulas all right I'm going to click on the link for video 20 now extracting a unique list here is a column and I need to extracting unique lists now if you can avoid using a formula here you should formulas are only for when you have very data that is going to be changing and you want to automate the process of extracting if you if it's a single time don't use a formula use a pivot table or advanced filter here's how easy it is for a pivot table I'm going to highlight this column and go to insert pivot table or alt NV t existing worksheet and I'm going to scoot way over here into a a a and then click okay I'm going to drag this ID column down to the row and that's it so that's amazing pivot tables are amazing alright and advanced filter' I tend to use this one a lot the pivot table has the drawback that you know once you have it then you have to copy and paste special values or something like that but advanced filter' will just flat-out take unique lists and dump it somewhere else I'm going to go to data advanced filter or alt a Q now it's got the right range I'm going to say copy to another location criteria there is none because I'm going to select this down here you absolutely can do criteria and extracting unique lists based on criteria but I don't want to do that and I want to paste it way over oops okay when there was this one I'm going to paste it right there click OK and then ctrl arrow arrow and there we go I love advance though so I use advanced filter to extract unique lists all the time oh looks like there's a blank there that got the blank too oh well we can make a formula to avoid that but that's no problem right we could filter it or delete it all right now let's go down and look at formulas again you just don't want to mess around so for example if you had a column and the dot is actually changing all the time or you have an expanding and contracting in a list of multiple customers or some sort of names right you could use a defined name dynamic range as we learned in video thirteen right so create that define name dynamic range and then use it and then as you enter in there's lots of duplicate than everything you just have this flat out formula that is expanding and contracting always showing you a unique list you could even do something like then use it for data validation drop-down list or something all right now we already covered this in great detail in eighteen we're going to use the frequency function number the frequency function is just amazing it only does numbers but no problem we know how to do this we're going to use the match function but we also have blanks so I'm going to say if anything in this range f4 is not null text string which means empty then please give me them match and the match I'm going to go ahead and use they're our robust formula in case we have wildcards double quote tilde double quote ampersand and then highlight this whole column here f4 comma and then highlight this whole column here f4 ampersand and then null text string comma zero close parenthesis all that's doing that's the value of true that false I want the false the actual word false to be put in there so I'm going to close parentheses and that data array f9 that's given me all of the relative positions control Z now and you just need all of the relative positions row of this f4 - row we saw this back in like video 8 + 1 that's how to all that's how to always create an array of relative positions Z all right so now I close parentheses now this little part here is going to be the internal part that helps us with our count unique count and our formula to extract unique records if I f9 this look at that anytime there's a number greater than 0 positionally that tells us where the unique item is all the zeros are duplicated we also learned that we can use the result of the frequency function in the if functions logical test argument any time we see a number not equal to 0 that is a true all the zeros will be false so we can use this not only to count but to get our relative positions for our lookup formula ctrl Z now we can simply do some and then if in the logical test any time it sees a nonzero number comma what do I want a 1 I leave the value if false off because I want that false close parenthesis and control shift enter that is our unique count formula we learned in video 18 now we want to apply what we learned from video 16 for extracting records all right you ready I need to turn this foil off when I get past the fourth row so I'm going to say equals if and I'm sitting in row in cells G 30 so I'm going to use my number incrementer G dollar sign 30 : G 30 and he that are going to be 1 2 3 as I copied down anytime that is greater than my hurdle f4 what do I want to show value if true null text ring otherwise please run the lookup formula so that the array is easy f4 comma it's the row number that's hard well no problem we either use aggregate or small I'm going to use small in this case this time here small and I want row numbers here so I'm going to use an if so for the array I don't want all of the relative positions I want just the ones that have the unique records so so for array I'm going to say if and lo and behold the logical test the string of trues and falses comes from that frequency function so I'm going to paste this logical test then the value value of true is just our formula element that creates all the relative positions but that little bit right there is the hard part right I'm going to control V oh that looks you know so huge here but just realize that's just delivering trues and falses remember there are nonzero numbers and zeros but value if true now I'm going to cheat here I have this this formula element that creates relative all the relative positions already somewhere else in that formula so I copied it all right that big hugh thing is just giving us the truth and false is now i need to type a comma and go to value if true ctrl d all right I'm going to quickly close that off because the falses will help us there and that array right there f9 Wow one two six seven that's exactly the relative positions of our unique items that the small function can then deliver to the index as a row number control-z absolutely amazing now decay I already have that here it's my little number incrementer comma k close parenthesis on the small rownumber it's just the small you could be using aggregate I like aggregate but not everyone has 2010 f9 there it is that's the first one when we copy it down smaller geta the 2 and then the 6 and the 7 ctrl z now row number close parenthesis on that the value of false it's just our lookup formula to extract as we copy down all of our unique items f9 so the very first one is of course that 1 close parenthesis ctrl shift enter and copy it down absolutely amazing if I delete all this I got that there ctrl Z if I change this to sell absolutely amazing all right let's go down and look at our next example and here's the deal that's a big huge formula if we could have a helper column we could make this much more simple let's scroll down here now back in video 16 when we talked about extracting records we saw lots of great helper column formulas now here's the deal at the end of video 18 for unique count we saw account if formula with an expandable range but here we have a single column right so I'm simply going to say count if and I'm going to put an expandable range in that range argument comma and then that as criteria now I've got to come back over here that's not an expandable range I need to lock the 54 but not that 54 so f4 alright so the range will expand as we go down the criteria will only give us a count of one when it sees the item for the first time control-enter double drag it down absolutely beautiful unique unique unique unique now we know from video 16 that when we have duplicates this is our lookup column right there's duplicates there that that means we have to do an array formula over here but we like to have a 1 a 2 a 3 and a 4 and we saw how to do this before we can simply first convert this to a true and false and then we'll use our sum function trick so I'm going to say anytime that is equal to 1 control-enter there's our logical anytime we have a comparative operator we create that logical form that delivers trues and falses those are the duplicates I was really talking about not the ones now we simply come up here to wrap our sum because we want the add the cell from above the sum function is great because it will allow us to ignore that text comma there's the number which is just true or false all the way down right now nothing because some will ignore it plus true is 1 as I copy this down this true plus that 1 will give us 2 right here are this false plus the two from above will give us 2 so boom there we go so 1 2 3 4 now just as we saw before cool the count formula is just max give me the max of that and then our lookup format is a straight index and the row number is match looking up the 1 2 3 4 in the lookup array which is our helper column and the trick is how do we get just the first one exact match zero absolutely awesome so this is amazing none of that a big huge array form it's just a simple helper in a straight lookup formula now let's scroll down and we'll look at our next example very similar we have IDs here but we don't want a unique list of ID's because then these two we'd only get the first one we want to extract the entire record so our concern is do we have unique record these two are unique because they have different dates and different calls these are not because all three elements of that record are the same as all three elements of that record all right three columns to check we're going to use a concatenated column to group together all three bits of information in a single column and then if any one of these cells are the same well know that the record is a duplicate now we saw how to do this type of concatenation earlier a few different examples now you could just join it oh but it's safe oftentimes to use some separate the two bits of data with some character now I'm going to copy this because I'm gonna use this a few times control V and there we go those three bits of information ctrl enter are put together double click and send it down we can clearly see that these two are not the same write the date the serial number and the number of calls are different now that's our criteria we could just it'd be nice if we could just use count if with an expandable range remember this and we could put our concatenated joined bit right here in the criteria but the problem is this range argument can't handle array calculations and we're actually going to have to join three columns so I'm going to come down here so again it would be nice if I could use that as the criterion count it but I can so I'm going to use some product now I'm actually going to type in a comma and keep that little bit in array - but I'm actually going to not use that array - I just want to separate it for so visually you see only stuff happening in this first array we need to join these three columns but they have to be expandable no problem I'm going to click the first cell and then shift : and lock the first one f4 now I'm going to come here and control-v there's our little join bit click on that cell : lock the first one control V click on that cell shift : hit f4 key to lock it alright so that that has three columns concatenated together this is a joint array operation you obviously can't do that in the range argument of countif so as we copy this down it will not only be concatenated but it will be expanding now we simply have to say is anything in that range equal to so I got rid of the comma so now it's all sitting in this one right now this is silly if I hit f9 it just gives me a true because of course these are equal to the it's the first time we've seen that record we need to convert them to ones and zeros so I'm going to use double negative open parenthesis and closed parenthesis now notice this right now would just give me a 1 right f9 but as we go down this array we'll have more and more numbers in it control Z let's see how this works I'll enter double click and send it down so here you can clearly see columns are expanding if I highlight this and hit f9 oh so the array as I copy down is expanding down here f9 there we go so that's a way to get around using the count if to count in an expandable range when we have an array operation alright so now what do we need to do to this we need to convert it to trues and falses and it's only ones were interested in so anything in there equal is the result of this some product calculation one true or false double click and send it down now we we have duplicates right we're interested in the truth but we don't want to build an array formula over here based on duplicate so we want one two three four so we do our trick we've seen before in earlier videos I say the sum of the one above comma and the next number will be our calculated logical as we copy down right now what are we getting some function ignores text so I get nothing plus true that'll give me one and when I copy this down this formula in this cell will say what's one plus true that'll be two down here it will be what's false plus two - so that would be a duplicate double click and send it down there we have it these are duplicates now we can simply use just like we did above now we have a mixed cell reference there though and in our match looking up the one in this column here the zero helps us to avoid the duplicates and it's just a regular formula it's not an array for now I'm going to copy it over and down alright so this solution is not an array for it but it is a great way to go now next we want to scroll down here and look at some sorting formulas now the most basic sorting formula is for numbers right and you could probably guess we simply use the small function of all the numbers and then use our number incrementer rows gives us one two three four as we copy down so we copy that down if you're going biggest to smallest you have large there's all the numbers and then our number incrementer now this is just four numbers and this is sometimes very helpful some of our later formulas will sort the numbers first and then get other data based on that but here's another example we want to sort the records based on the number column so I really want Carlo to twenty at the top then belen 20 and then Balan 22 quad 35 five so sort ascending now we'll see a helper column solution and then we'll see an array formula down here now how about using the rank now in 2010 they added a bunch of new statistic functions with this new dot extension rank is the old function work still works fine rank dot e Q is equivalent that means it will rank ties equivalent an average means it will average them so you can either use rank EQ or the old one the number that's the number I want to rank the reference that's all the numbers F for and then in 2010 to have these great dropdowns for descending or ascending I want ascending now of course this will give me a tie for a rank so we need to amend this so that we have one two three four in this column I'm going to come up here and let's think about this if we did a count if function with an expandable range I could get a one for duplicates and then a to still further if I took that same idea count if and in the range instead of doing expandable range starting right there I could start one above and then colon comma and our criteria will be this 20 to the advantage of starting the expandable range one above is that when it sees the number for the first time it's a zero and guess what since rank is giving me one one I'd like one plus zero and then one plus one if and if there were other duplicates that have one plus two 2 etc I need to lock that f4 and you got to be kidding me ctrl enter and copy this down alright so see here the count ff9 is given a zero escape here the count if f9 is giving us one escape now we just do a basic formula notice our helper column gives one two three four what are we going to have to have something to turn it off and on like an F or an affair because we want all of the records sorted based on this on number calling so the only trick is this array right there it better be for us and this this formula if I just want to use just the row number hey that's a mixed cell reference right so in the formula copies over the blue one will move to the count column and then match I don't have one two three here like we have had in many other example but I use my number increment oh this gives me 1 2 3 4 etcetera ray that's our helper column 0 why because these are not sorted control-enter and that's a beautiful way of doing it if you can have a helper column perfectly sorted if these numbers change it absolutely updates controls people love these you know you have a template with names and scores right and so the templates always changing the names and the scores but then you want everything sorted beautiful use for this type of formula now if we can't have a helper column which is sometimes the case we can do an array for that now the cool thing is here we can use our small to extract all the numbers small and rows including the ties now our data extraction formula here to get the names associated with these is going to have to deal with ties again if we're doing a lookup formula and you have ties you're always going to have to have do some fancy thing to get around it now I'm extracting all the records so I don't have to start out with if rows greater than or anything I just do index and then the things I'm are extracting are the items f4 comma now row number there's multiple row numbers because there's duplicates so we're going to have to either use small or aggregate here in this example I'm going to use small and the next example will have another similar example and I use aggregate small can work in any version at aggregate can work in 2010 or later so small now what is our we remember small delivering row numbers as we copy them down the first second third so we want ultimately just some of the row gnomes right now we want just the ones for twenty right which would be relative position two and three so I'm going to I have a condition here right I don't want all the numbers I want some condition now here's the column with our criteria now I'm going to say anytime it's equal to relative cell reference one to my left comma and then value of true it's the same old array of all the relative positions that we've been doing quite often so the value of true f9 that gives me all the relative positions control-z now I can come to the end the false will be put in because I left it out and now the array f9 absolutely beautiful I have a two and three when I get down to 22 there will only be one number here but again the beauty of this is you could have lots of duplicates and small has the right set of row numbers now we know we go comma and the call the K now get this so far every time we've had a data extraction formula we put rows or columns for the K think about this rows of this cell would give me one two three four it would work for the first two but since 22 has only one relative position that means the small will be spitting out only a single number if the K all of a sudden has a three there you get a number so in this case you use count if with an expandable range close parenthesis oops I mean comma so right expandable range f4 to lock that think about what it does one two one one absolutely perfect for the K and small all right close parenthesis on that small now we have that whole row number again we've seen this so many times in different form the small f9 is just delivering a row number to index control Z all right close parenthesis and we definitely have to use control shift enter why because the logical test has a array calculation control shift enter and double click and send it down so we get the same data as up here but we have no helper column we'll change it it automatically changes I change this to and it changes everything beautiful now in this case we're extracting all of the records but oftentimes like in sports templates and whatnot or sales you're not interested in all of them you just want to see the top 3 or the top 5 right the trick here is when you have duplicates you can't build a formula based on top 3 you can't have it some formula it says if the rows are greater than 3 then please turn the formula off because if you get a tie for third there could be more than 3 values so when this happens it run a count yeah and the range argument is simply going to be our numbers and what are we interested in top 3 right but for the count we have to say greater than or equal to the third largest so comma the criteria is going to be greater than or equal to in double quotes and join it to the output we get from the large function I'm going to say hey what value in there is the third largest so this way we can deal with a tie the criteria argument has a comparative operator join with the output from large and how many are there there are four now this formula on this column is straightforward remember a moment ago we said we will use the large and small functions in various ways here I'm just running the large with our number incrementer and I'm turning it off when it gets past 4 if we had just said greater than or use this 3 here we would show only the first thing we would have missed the duplicates now we're going to do a similar formula to the one we just did above except we're going to have to turn it off when we get past row number four and we're going to have to run our count ifs with expandable range again equals if and you know what I already have something over here I could say anytime that is equal to a null text string because these are no text strings comma then what do I want well I want to know text rings I want these to show nothing otherwise the value if false will be our index the index I'm extracting the names f4 comma now we need row numbers for 22 we're going to need row 1 and 5 right and then for 15 we D 2 and 4 if I my eyes are correct there so I'm not going to use small I'm going to use aggregate aggregate is in 2010 15 means small 6 means ignore errors because we're going to get divided by 0 errors and the array well I'm interested in the row number so I have to do our formula element that always gives us an array of all of the relative positions all right and I have to put it in parenthesis then I have to divide by our criteria anything in here F 4 equal to the one cell to my left close parenthesis alright so comma actually why don't we look F 9 right 1 and 5 divided by 0 the 6 we'll ignore them the 15 will act like the small function control-z and comma decay count if expandable range and I better expand it by locking the first one by the way if you lock this one and not this one when you enter it and copy down the locked one will switch to here right so there's our K it will give us one two one two absolutely beautiful close parenthesis on the row number the aggregate is just delivering a relative position or a row number to the index closed off index closed off the if and I don't have to use control shift after just control enter no curly brackets up there double click and send it down and right so if we were to have some other ties or control-z no tie at all boy that works perfectly control Z now all of the sorting we've been doing so far is based on a number column right so let's scroll down and see how to sort a sorting formula based on a column with text so we'd like to sort this whole data set but have Belon Belon Carlota quad now we're going to use a helper column and then we have some ridiculously huge array formulas to do this this is not an easy task actually what the helper column it is pretty easy well let's do count if and the range I'm going to count all these f4 comma but here's the criteria I'm and ask the question less than and join it to relative cell reference now check this out we are allowed to do comparative operators on text items right because this is the alphabet how many of these items in this column are less than a B word none zero these two are tied right so that's going to get a count of zero and zero but when we copy the formula down how many words in this column are less than carlota the two here so it's going to get a two right here when we get copy this down here I want to get down to quad how many words are less than quad three now that's not going to work but we can amend this formula what I would really like is one two three four well guess what if we add an expandable range and count if that we've done a few times already for a unique list because how many bail-ins are there one how many bail-ins are there two but since it's counting a relative cell reference I'm going to get 1 1 2 1 and that's perfect it's adding 1 to everything and any times it sees an extra duplicate a second and third 1 and add 2 3 etc so plus and then our count if our expandable range and I lock this right 1 2 1 1 absolutely awesome look at this push 1 2 3 4 now I have a straight lookup function here relative cell reference for that blue range match has number incrementer and it's looking through this column right here control-enter none of this is array this is just a help cool helper column and a straight lookup function now this uses a helper column let's see how to do this without a helper column and this fun this form is going to get pretty well we have straight text here names in a column and actually I saw this formula learned it from Richards scholar at the mr. excel message board absolutely an amazing formula now the int I've done some unique count formula here so I know I can turn this formula off when I get past the fourth row now just a moment ago we did count if so that one of the inside portions of this data extraction formula is going to be the count if F for comment and guess what the criteria argument is going to be the same or similar to above the same part will be we use less than and we join it to the whole column so where as above we copy the formula down and got a bunch of cells with values right now we're going to have because by the way that criteria argument we're doing to array calculations we've done an array joint operation and we put eight values I think that's eight values into this criteria argument so of course eight values and criteria then the countif will spit out eight values F nine and what this says is since chin is the smallest amongst all of these words there are how many less than it one zero there's one two three chin so there's one two three zero so the zeroes are in the position where that item is now if we use the match function to look up zero right now we would tell me position two and then I could use index and it would take chin and put it right into this cell H one fifty two but of course that wouldn't work if we copy down but let's think about this when I copy it down who's the next person sorted here it's Phil and look at this array if I was looking up zero match was looking up zero what would I have to subtract from that number right there Phil what would I have to subtract to get a zero in that position when the formula is down here I'd have to subtract three and guess what how many Chin's are that one two three so just to show you here before we do the little formula element let's just see what happens if I subtract three from that whole array this countif is spitting out an array I am allowed to subtract or do any math operation on a single number right on the array f9 there's the zero in position one let's take it one step further what would I have to do f9 to get a zero in the fourth position because sheila's is the third name I need to extract well of course control-z I'd have to subtract four so if we could do that if somehow we could subtract zero and then three and then four as we copied our formula down from this array we'd always get a zero in the correct place control Z well guess what we're going to use another count if I told you this was a wild one and the range of things we're going to be counting is all of these names f4 comma and the criteria well remember I need zero and then three and then four well right now what if I just put in the criteria this range and it's going to have to be expandable and lock this well right now how many names are over here zero but when I copy this formula down this countifs will be down in H 53 right and it will be looking at Qin's name from right above and how many Chin's are there 1 2 3 not only that but when you get down to h1 54 where it should be extracting Sheila's name this range here will expand and now it will be looking at chin and fill well criteria argument if it has two numbers in it it's going to actually have three that have a 0 a 3 and a 1 what's 3 plus 1 it's 4 so I simply need to add a sum around the count if now this is an amazing little construction right here as we copy it down the array of numbers containing our zeros we'll be changing that whole thing right there is going to be lookup array inside of match remember we want to look up a zero comma in that lookup array comma exact match oops comma zero close parenthesis so the match now is going to be inside of index index is what is looking up these names f4 comma that whole thing is inside as going to calculate the row number match with zero lookup value and this changing array now it'll be easier if we enter this and this is an array calculation there's one right there that's going to require control shift enter and a bunch of and another one also so control shift and enter wow it's so amazing now here's the magic right when you get down here and let's say that let's um let's look and see what count if is giving us this is the expand line now it's looking at names and chins and counting how many over here f9 it's giving us a zero and a three the sum is going to act while the sum will add it let's go down here to this one count if is going to be spitting out f9 the an array of numbers the sum is going to add them and from our this is all in the lookup array for match OOP so now I f9 this and the zero has changed it started off in second then it went to first and now it's in four matches looking it up match will deliver f9 a4 to the index absolutely amaze now I'm going to click escape because I did a bunch of f9 ins and you only have one undo in edit mode now all we have to do is turn this off equals if and I'll leave my number incrementer rows H dollar sign 152 : H 152 close parenthesis anytime that is greater than our hurdle f4 then comma the value if true null text string please show nothing comma otherwise we'll take that lookup formula and calculate it control shift enter double click and send it down absolutely amazing if I come over here then type Tommy Tommy appears at the bottom if this is now a bow Abe is at the top that's amazing it's it's like sorting and getting rid of the duplicates and extracting a unique list controls easy absolutely amazing now this formula will not work on empty cells and not going to work on other types of mixed data and the countifs take a long time to so we have some array calculations here so if we could have an alternative formula to this that would be great now if you thought this formula here was wild wait do you see this next formula and this next folder will handle the mixed data and empty cells and actually this formula comes from dominique at the mr. excel message board now here's our data set and we'll add a blank a little bit later but right now we have this data and the problem is is if we use count if on this mixed data it's just not going to work so here's the question we're going to ask then we're going to get a formula to answer the question I want to ask how many of the other items in this list is this one greater than now this text item is greater than one two three if I ask the same question here hey thing how many other of these other items are you greater than the only one this is greater than is that one so if we could get that information it would be great and the way we're going to do it is we're actually going to take this data set and transpose it and then compare these different orientated arrays using an array calculation and getting a row of 0 1 1 1 which will represent 0 for this this item right here this item is not greater than itself but it is greater than 1 1 1 so the 0 1 1 1 will be orientated horizontally we will then gather them up using the molt function like we learned in last video into a column that'll say 3 1 2 arrow representing the number of items that it's greater than we can then use that positional information to extract a unique list now let's just do this in the cells first I'm going to transpose this data we're going to type my formula to compare this range and I'm going to say anything in their array greater to greater than this now these are two different size ranges there's four rows here for comm so it will result in a four by four this is not the doing the matrix multiplying this is just straight and we haven't seen a comparative operator I don't think done on two different arrays like this we didn't subtraction and multiplying but this will work and I want to convert them to ones and zeros so I'm going to do plus zero control shift enter and as we talked about last video gathering up a bunch of things in a row adding them we saw how to do that with ammo mole function all we need is well this is a four by four we need a single column with four rows of ones multiply those doing maker matrix multiplying and we have our deal now how are we going to get a column of ones I'm simply going to say hey all of those rows that gives me 175 to 178 but we raise it to the zero last video we saw how to do this with columns right and then we can simply to gather up in just as if we have these numbers next to ourselves we use the moment and take array 1 times array 2 control shift enter and there's our 3 1 to 0 now we have to do that all in a single cell watch this remote the first array M and say oh this are you greater than transpose and I do have to put that let's put that in parentheses and plus zero comma and then our second array there's our first array our second or and our first array is f9 that whole thing right there control Z our second array is going to be row caret 0 close parenthesis now that of course will give us the f9 all on a single cell right I'm going to control shift enter now one other bit that we have to do in case there's any blanks we're going to have to replace that range in that range with an if statement and here's the if statement equals if anything in here and I'm going to hit f4 and I should copy this little music anything in there is not empty please give me that whatever's in that range close parenthesis now this little if is going to go in place of that range right there control V control V and then I forgot to lock these kidding me this is this is huge f4 now this f9 is still giving us the same thing but it's more robust it's going to deal with the blanks guess what we're going to have to use this a few times in this formula over here so I am in a ctrl C to copy it ctrl shift enter ctrl f3 to open to find names I don't want to create a new name I'm going to call it BT or bigger than highlight down here control V click OK oh I can already see there's a problem sometimes with vehicle formulas it thinks it's not legitimate and they put double quotes in so I'm going to make sure I get rid of those and then I edited it down here instead of clicking the Edit so I'm going to click the check and then there we go click close let's check it equals bt it looks like it is working if i highlight this and hit the f9 oh that is amazing so bt is what we're going to use over here now this is going to get tricky alright before we create our lookup formula right there I want to move these values over here just so we can see them while we're creating our formula all right index we're going to do lookup in the array these are the values we're trying to extract , the row number is going to come from match and match is going to be looking at these numbers in that bt range but the tricky is as we copy down I'm going to name the first zero or the zero the position of the zero the position of 1 the position of the two etc so match and the lookup value is going to be tricky but the lookup array is that bTW and I'm going to go ahead and put my zero and close parentheses alright so what the world we're going to do here well we're going to get the minimum value from here but as we copy our formula down we're going to need to exclude values that are already used so I'm going to have the min function here and two if conditions one's going to say not empty and the other one's going to say it hasn't been used yet already from our extracted list alright so in the lookup I'm going to say min if and then any of these f4 not empty and then comma I have a second if and the second if it's going to say I haven't already extracted it now how do we compare to less back in video 18 we saw we could use is n/a and match function together so I'm going to do that here is in a and then match now wait a second we're comparing a list that hasn't been extra acted yet to this full list and guess what the lookup array is going to be all of the values yes there's going to be an array argument calculation here f4 and I'm going to compare all those values to an expandable range from the cell above so now I'm going to : and then comma 0 for that match and right here that lookup array needs to be expandable so I'm going to lock that first one now what this is doing is this is how you compare two lists here's all the values that's all of these and this is expandable where right now is X the word extract over here no so all of these values are still up for grabs which means all of these numbers are up for grabs but what happens if I come over here you can see that I need to put a closed parentheses there is an a by the way it's the match is going to deliver four values because the lookup value argument has four values so the is n/a will give a an array of trues and falses f9 right now it's all truth but as I copy this formula down here and it sees the first item which will be this it'll exclude that and these will be the only ones available for the min function which means then the min will pull the one control-z now that's the logical test so we have to come to the end here remember the min has 1 if - if to isolate and then comma to put just the values we want bTW now I can close parentheses on the first if close parentheses on the second if and here that min I'm looking at that purple one I need to put one more right here you know if I type it here and I got my eye right here I can see that it's bold all right so I think I missed that one now they look that lookup value when I hit f9 should give me zero which is exactly what I want none of them have been isolated so mens looking at all the Vice control-z now I just have to come to the end that's the mass bloop so I come here close parentheses on the index control shift and enter you've got to be kidding me now I want to come here and look and see what in the world this is na is giving because that's one of the things that's isolating which numbers we pick f9 so now we have to to false so that one's been eliminated control Z escape when I come down here and look at the logical test number it's comparing two lists you can see that expandable range right there that's the expandable rage we're compared to an array argument out the calculation right there by comparing this to this f9 so now we see false and false because that one has been eliminated and this one because that means that number and that number eliminated so the only two numbers left are three and two I'm going to control Z and actually let's see if we could inside the men this should give this as an array calculation two ifs and an array of value so I'm going to hit f9 now I have false false the three and the two are the only things being plopped into the min and that's what match is using right f9 so it's looking up a two and what values are here f9 to that exact match is helping us out there right because it's not but that is a pretty wild for me I'm going to click escape now I'm going to do one last thing if throws our number incrementer I'm in B 183 : anytime that is greater than this for F for common and double quote otherwise wow that big huge thing right there ctrl shift and enter so and then delete so robust fine you know we didn't even check this out if I put delete here this you know this one shows all zeros ctrl Z if I start putting numbers here right then it just goes wild all over the place so control Z Z so that form it is awesome because it's a little bit easier than but you have to have only text data and no empty cells whereas this one oh man that that is one big huge formula but it seems to be working all right video xx that was all about extracting unique records and sorting formulas when we come back in 21 I can't even remember what 21 is o conditional formatting with the Ray formulas alright we'll see you next video

Channel: ExcelIsFun

Views: 37,399

Rating: 4.9179487 out of 5

Keywords: Excel Array Formulas, Array Formulas, Microsoft Excel Array Formulas, excelisfun Array Formulas, Advanced Formulas, Mike Girvin, Mike Girvin Array Formulas, Mike excelisfun Girvin Array Formulas, Ctrl + Shift + Enter, not just enter, Ctrl Shift Enter DVD, Ctrl Shift Enter Book, Extract Unique List with Formula, Sort with formula, INDEX function, MATCH function, MMULT function, Helper Columns, Sorting Numbers, Aladin, Domenic, Richard Schollar, Mr Excel Message Board

Id: J6yeTUFkLzQ

Length: 54min 45sec (3285 seconds)

Published: Thu Feb 13 2014