Excel: Find Multiple Matches & Dependent Drop Down List (Unsorted Tabular Data Set)

in a previous video we used some rather complex formulas to get a unique drop-down list that's fully dynamic now the challenge was that the data had multiple instances of each name and these names were also not grouped together so they were just like randomly on this list here's how it looked I'm also going to add the link to that video in the descriptions we also used Excel table feature to be able to add more names and have them reflected on our drop-down list then the next question came on how could we create the second drop-down that's dependent on the first drop-down and based on the same messy data set so for example if you select productivity from this list we want to see all the apps that belong to productivity if you select genes you want to see all the apps for games and so on [Music] so let's see how we can set this up with a formula that doesn't require control shift enter let's just take a look at the data set that we have division is listed here it's random they're not grouped together we have the apps listed in front of the division I've created a table out of this called table div in a previous tutorial we sought for getting a unique list out of the division so in this case we just have three divisions here if we got new divisions added to this list our data preparation table which was actually the driver for this drop down would update automatically as a result our drop down updated automatically because behind this there's the offset function now if you miss that the link to it is in the descriptions but in addition to this I really recommend that you watch the video from Kevin larvas where he included many different ways of getting the same thing done so if you have a huge data set and this formula approach could be a bit slow for you I do recommend that you check out your other options from that video and I'm gonna add the link to that as well in the descriptions this video is for those people who are interested to see how you can get the dependent drop-down part also with formulas okay so right here we're gonna have the apps that belong to the game utility and if we switch the productivity we're gonna have the apps that belong to productivity so my approach here is very similar to what we did to solve for this one I'm also going to use a data preparation table and get the list of apps in the data preparation table and they make a drop-down based on that this data progression table will look slightly different because it's going to be going this way instead of vertical right but that doesn't pose any problems for the data validation right if we go to data data validation and we select list it doesn't matter if our list is vertical or horizontal it's still going to show it to us in a vertical manner right so if we manage to put our list in a horizontal way in front here and then we also use a similar function like offset to get the correct list shown then we've solved this problem next challenge to solve for is the formula that we need for this and I mentioned that I want to use a CSV free version of a formula so this means I have a choice between index maybe some product maybe the aggregate function so since I used index here and index is going to work well with getting the answer from the app column I'm also going to use index in here right because if I managed to find the row that the apps are sitting on based on the division here then I'm all set remember what the index function needs is first is the map and then how many rows it should go down right so if it checks that this is for the activity and it notices ok the first occurrence here is in position 1 then I have my answer second occurrence position to 3rd position 3 and then it needs to skip position 4 because the next occurrence is actually in position 5 right so the challenge for the index function here is to find the correct row number I'm just going to put the rows that we would need for productivity for example in here just so that we can double-check our formulas later so that would be 1 2 3 & 4 is not a match right because 1 2 3 so much this one is game right so for productivity this will not be a match then we need 5 and then we need 6 & 7 is not a match and then we need 8 here so something like this is a sample data set that we can compare to when we managed to come up with a formula that gives us these numbers so what formula could we use that is also CSV free I can think of the aggregate function because inside aggregate you have this part here that can handle arrays I would probably go with small to get the apps shown in the same order that I see here right and the good thing with the aggregate function is that you get this additional options of ignoring error values and that's something that could come in handy when we also select small and we put in our array the last argument is if we want the smallest the second smallest third smallest and so on and that's something that we can also make dynamic but even if we have an idea about the formula that could work the challenge is to get this array part right that is going to give us these numbers inside the array okay so let's start with the core first let's look at how this array could look I need to check if division can I'm just going to click on top to get the whole column of the table if division equals this okay I'm just going to press ENTER now this is an array formula all written in one cell to see what's really behind it we need to click in the formula bar and click on f9 let me just expand this that we can see the whole data set we see that for the first occurrence is true true true false true right so that's exactly what we see here now it would be great to translate these two row numbers so I'm going to press ctrl-z to go back to my formula and multiply this with the row that they're sitting on okay but I'm just going to put this in brackets and now let's press f9 we xena The Associated row number of productivity right so four five six then it's zero because there's no match there eight nine zero and so on that's good but if we're planning to use small on this data set then small is going to always find all the zeros right we want to get rid of the zeros and best would be since aggregate can ignore errors that we actually generate an error wherever we have zeros to do that we can divide this which is the result of our true and false values with itself and then multiply it with the rule why because if we divide a true with the true we're dividing a one with a one we get one if we divide a false with a false we're gonna get an error because we're dividing 0 with a zero right so if I just highlight this and press f9 you can see one one error one one and so on now if we multiply this with the row we should get the same thing but have the zeros replaced with errors okay so we're in a good track since we're planning to feed these later on to the index function and if your index in this area we need to start with one two three and so on we need to make a correction to these numbers so that they also start from one here okay so I'm just going to press ctrl-z to go back and I need to deduct the header row so I'm just going to click on this one right here close bracket and since we want this result to be then multiplying with this one I have to put the whole thing in its own brackets okay so now let's check one two three error five six very good then it's eight now we're on the right track we can plug this in the aggregate function but let's just think about the fixing as well because I'm planning to pull this across and pull it down as well so I don't want this e8 to move so I'm going to fix that but I do want to move down right so I just need the column to be fixed here as well the rest our table references so let's see how they behave later on when we pull this across and down let's plug this in our aggregate function as the array part we're going to go with number 15 which is for small then we're gonna ignore errors now you can also pick 6 or pick 3 which ignores also hidden rows then we want our array which is this then after this we want our K so if it wants there one smallest basically the smallest or the second small is third smallest and so on we want this number to change when we're dragging this across so here we can use the columns function and just take a look at the column above and fix the first cell reference the second one I can leave as is but if I don't want it to come down I can also just fix the 7 and leave the column variable okay but that doesn't matter you can also leave it fully variable that should take care of the aggregate let's just check what happens if we pull this across okay so this looks good this looks good these don't look so good so what's happening here it shifted to the app what's happening here it shifted back right so tables behave in this way that when you're pulling it across your column reference is also going to move and then when the table ends because you can't move anymore it's going to go back but we don't want that we want to fix the column reference to division and fixing in tables means that you need to get that square bracket header part and do a colon and then reference it again so it's like division to division kind of like when you reference an entire column like for this one it would be K to K it's the same except that you can't leave the formula like this because there is an error in here it doesn't accept it you need to put them in square brackets again so that hole reference in square brackets and now I can leave it the main challenge here is to find your square brackets on the keyboard I'm just going to update all of these ones that need the fixed referencing for the header since I'm only interested in the row number if the header switches to here and back it doesn't matter okay I can let that switch now if you pull this it looks good it's exactly the numbers that we want so now we should be able to plug this inside an index function we need to index up here because our answer is an app but again we don't want it to move back and forth so we need to make sure that we have a fixed header here and do this special referencing with the square brackets and that's it and let's close the bracket there and see what we get okay let's just check these when cow blend voltage twisters skipped in key spokes kind Abe and so on guess what that looks really nice but now the thing is that if we add more productivity apps to this we want a formula to pick it up so we do want to leave enough space here just like we left enough space here to account for new divisions so I'm gonna pull this across until where I think I might have apps being added okay so I'm gonna go until here now we get errors wherever it doesn't find anything and we can correct for these errors instead of using if error let's do a count can count how many productivities we have on this list and we'll make sure that the moment our columns are more than this count we're just going to write nothing to the cell instead of the error let's just use this helper cell here and do a count if our range is division and our app is this one guess I'm just going to pull this down that's the count so let's add our if to this if the columns here you can just copy this formula is less than or equal to our number here and again let's think about the fixing we have to just fix the column but not the row then it should do the index thing otherwise it should do nothing now let's check looks good and the real challenge is to make sure everything else updates as well these look good as well so I can get rid of this helper one now that I have my app list here I just need to create a dependent drop-down here I will also use the offset function just like I did here except that it's gonna have a bit of a twist to it we need to figure out which role our list is sitting on and how many columns our row has we can fix the columns to 20 for example in this one but we'll just see the last ones the last places as empty on our list and you'd probably want to restrict that to just the number of apps that you have under the list right so let's see how we can overcome these challenges I'm gonna type the formula for the validation list in here first and then we can copy it to the validation it's just easier to track so offset our starting cell can be this one I'm gonna fix it then how many rows do we want to move down so which lists do we need well that depends on our first selection right so I can match for this one and I'm gonna fix it in this list fix it again and I want a perfect match and for the columns how many columns do we want to move well we don't want to move any columns we want to include all the columns including all the columns is the width argument of offset for the column argument since we don't want to move anything we can skip it height is 1 that's an optional argument we can skip it with is how many columns do we want to included so the easiest approach is to accept those empty ones and just put 20 in there because if I click on this and press f9 I can see the list and I see the empty ones so controls that to go back but the better approach is to also restrict this now the formula that I came up with to restrict this is a coming of countif and offset and since this video is already getting too long I'm not going to explain this in this video but I'll create a second video on just this concept how you can restrict your data validation lists to the number of categories that you have in a separate video and I'll try to get that out in the next month or so if for now I'm just going to paste the formula in that's the formula if I click here and press f9 we can see that now it's restricted to our list let's fully test this by copying this and pasting it in the data validation box go to data data validation click on list and paste this in now let's just double check when cow blend let's just compare this to our original list as well okay that looks good if I change this to game that's twister fighter hacker that looks good as well okay so one last check is adding stuff to this table let's add a new game to this and let's call it rocket and also let me just add a full new division and call the app hello okay so they pop up here rocket has popped up as well so I'm already in game let's just check rocket is right there let's see if we have the new one and hello is right here how you can create a dependent drop-down list and an unsorted table like this one now it's a bit complex to set this up so feel free to download the workbook and use it as your template as always if you liked this video don't forget to give it a thumbs up and for more videos like this one don't forget to subscribe to this channel so that you can get updates when new videos come out [Music]

Channel: Leila Gharani

Views: 155,112

Rating: 4.9660215 out of 5

Keywords: TabularMessyList, dependent drop down list, find many matches, Excel match multiple results, INDEX formula, exlude empty cells in dropdown, excel table absolute reference column, Vlookup many results, AGGREGATE function, Restrict drop-down to non empty, Excel table references, OFFSET formula, XelplusVis, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts

Id: gu4xJWAIal8

Length: 19min 18sec (1158 seconds)

Published: Thu Mar 15 2018