QUERY - Drop Down List to Filter Data - Google Sheets

alright so in this video we're gonna incorporate query function with a couple of drop-down validation lists and I'll show you how you can make it happen so this is a little bit of data you can have more or less it's really up to you at that point so I'm gonna just add a worksheet here I'll call this one brave port it should be good enough this is where we're gonna run our setup basically so I need a couple of drop-down validation lists I'll make one for region and another one for sales rap and before I do that I'll make one more tab and I'll call this one validation and what I'm gonna do here I'm just gonna basically get a unique list out of here and another unique list out of this sales rep column so I'll go back here basically skip the first row here and run unique function and go to my data and select starting from the second row and down remove the end reference close parentheses hit enter that should give me a list of regions I'll do another unique function here go to that same data tab select a little bit here uncheck the last row reference and then close this hit enter that gives me a nice list of my reps if you want these sorted in some sort of alphabetical order you can also just go ahead and sort the list by wrapping this entire thing inside of a sort function something like this same thing we could do for this one so sort that I have my lists and the reason I left this row on top open is that I want to have an option for all regions and all sales reps in my drop-down so I'll do all regions basically just manually type that and all wraps like this I'll just make it bold to make it look nice but it really doesn't matter it's just gonna be one of the options of our drop-down here so now that I have this tool list for my validation I'll go to my report and just do something like region here rap here and right below them for region I'm gonna go under data go to data validation and that will be a list from a range here I have to select a range so I'm gonna click on this little whatever this thing is and go to validation and to make this easy for me for future updates I'm just gonna select this whole column a hit OK and reject all the other input go ahead and hit save so that's the first drop down validation that gives me whatever is in that column now I'm gonna do another one for this one so data data validation again list from range kind of repeat this whole thing click here go back to this and let this time we're gonna select the B column it okay reject all the other inputs save so in my report now I have my two drop downs now let's run a pretty standard query function from this data tab and just filter it to let's say Midwest and Jose so I'll go back here and do equals the function and the first argument here is the range of the data I'm gonna go to data select with headers a little bit like this remove the end row reference to send this all the way down comma and then I need to select statements so for now I'll just do select star that will be all columns and then I'll do a filter so I'll say where the region column is B so where B equals two mid-west and by the way I have to use single quotes here because we already have double quotes for our string so that's Midwest and I want to make sure the C column equals two well whatever that is going to be so that's the name and then I'll do double quotes here to close the Select statement which is the string that started in here and then finally we need the last argument here which is the number of header rows so it's just one had a rone top close that hit enter and that should basically give me all the matches now one thing to note here is that if I type Midwest like this in a lowercase M C I get no returns because this is case sensetive we might want to make sure we don't do this as case-sensitive match so one way we could achieve that we could just wrap this whole b column in lower function to make it lowercase and then also make whatever's in here in lowercase and now if I enter C I still get that return I'm gonna do that same type of thing for my salespeople so I'll do lowercase for column C which is the values in a column and then do lowercase for the actual value that we're looking for so now that we have this all set we don't want this to be all hard-coded in this formula basically we want to be able to replace this part that says Midwest with whatever is selected in here and we want to grab whatever selected in this column and kind of just filter to that and give the results so what we need to do we need to dynamically basically just create this string now I'm gonna just build this piece by piece so the first thing I have to do I have to make sure when this is selected as all regions it's just not filtering to anything and then if it is selected to something that should give us the filter so I'll just keep this as is right now I'll leave this alone I'll come back to that what I'm gonna do I'm gonna just make a couple of if statements here on top so I'm gonna say if this equals two and that's all regions comma then we want to just keep it basically blank so I'm gonna do double quotes comma otherwise I'm gonna give it text I'm gonna say and and I'll probably add a extra space here and the region column I think it was B so B and we decided to make it like this so lower B equals to lower again that lowercase now board should be in single quotes so there's a single code so this is where we usually have that like Midwest or whatever is supposed to be in here and to get that I'm gonna close the quotation here for this string that was started in this left side of this and I'm gonna concatenate when an ampersand whatever is in this cell and then after that I'm gonna concatenate again because that's just gonna be the region itself like Midwest now after the region R we're gonna go back to those double quotes again and close the single code for that region text and close parentheses for this lower function I'm also gonna add an extra space here and do a quotation so close my if function hit enter so right now you can see my if function returns a blank cell but if I go back here and basically select one of these like South C it gives me and lower B equals lower self now I need to make sure that that column was correct so yeah that was B column good so that's good for this so now if I checked Midwest this will do this but if I go to all regions it will just be blank that's good I'm gonna basically just copy this hit escape paste this right below and do this same sort of thing but I'm gonna change it for the rap so I'm gonna say if it's all raps I think that's what I called it then I'm gonna leave it blank otherwise I'm gonna take I think it was column C now and we're gonna take the lowercase text from that B to sell so now if I had enter it's gonna say basically this let me just take this formatting off it's gonna say and lower C basically equals that and if I go back to Grace that's grace if I go back to all wraps that's not what we're looking for let's go back oh we need to check this is not in a - but B - so this should have been B - so C now all wraps it's blank if I change the grace that's gonna give me this if I change the Jennifer that gives me this so now I have two if statements let me change this one to something else - so we can see those output so what I could do now I could kind of join this - if statements together just go back in here copy this if statement without the equal sign hit escape go back to this if statement on top and just join the second one with an ampersand with a concatenation the second if statement right after that the one below I'm gonna hit enter and what it's gonna give me is this it's basically giving me this and lower and I don't need this anymore so it's basically the Midwest filter and the Jennifer filter now what's gonna happen if we change this to all regions see it removes that filter because we kept it blank in this case and if we go to like Northeast that will come back and the same type of thing is gonna happen here if I do all wraps all those are gone if I do Isabella basically just plug that back in if all of them are all wraps it will basically all regions again it will basically just be blank so that's that part of this I'm gonna just bring this back to something that we can see what's going on here so that's that now if we look in our original statement here we basically said select star we're lower B equals this and lower C equals this and this is a little different in a way that there is an extra end and I did that and on purpose because I'm going to need that one and the reason I want to have that is because I want to have the option when both of them are unselected so if both of them are blank basically we're gonna end up having this whole string is gone and we'll have the whole where statement still in there and if I had enter right now this is not a valid statement so it will just break down so we want to make sure this works so we need this where statement in here but we don't want this to filter to anything so what we can do we can do this little trick here we can do where one equals one which is always true so that will basically just give us the whole data set from the tab here because one is always equal to one and to this we can always tag along that and stuff with an end lower B is this and that is this a knife I entered that will filter this too well it was Midwest here and Jose in here right so that works well so now this part this end and this is pretty much what I have here on top and I can replace it with my formula that's exactly what I'm gonna do I'm gonna go here grab this entire if function not including the equal sign copy that hit escape go back to this formula remove everything after this one equals one which gives me basically everything if I don't continue with anything else but I am going to continue by pasting after the space my if function concatenated here so ampersand right after the quotation is closed and paste that entire if function right there I'm gonna hit enter and we can see this is basically now Edward Midwest because that's what's selected on top there I don't really need to keep this on top I'm gonna remove that and let's see how this is gonna work let's make this a little smaller so that's good so now if I change this to Northeast you'll see it's northeast Edward results apparently there's just one let's find something that they're a couple of results at least so for example if I do west let's see West do we have multiple for West well not that I can't see but if we do this one see that should be at least two of them so let's do that so I'm gonna do Lance so I'll go here and find that and there it is we get all the results for North East for Lance now if I change this to Midwest apparently there is nothing if I change to West apparently there's nothing too now if I go to all regions it will just give us all of it so apparently they're self now if I go to South Sea that will give us self now if I change this to all wraps then it will give us all wraps and just South here and if I change this to all regions it will be the whole data because basically it's that one equals one scenario and this way we can have our dropdowns and we can filter our data set based on what's selected here and we can also have like this all options located on top and that's pretty much all I wanted to cover here thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Spreadsheets

Views: 145,209

Rating: 4.9447618 out of 5

Keywords: QUERY, Drop Down, List, Filter, Google Sheets

Id: nLW8SerwnJo

Length: 15min 24sec (924 seconds)

Published: Wed Nov 27 2019