ImportRange with Sort and Unique Functions

we're gonna be using a mock-up of one of our student referral systems that we have in place and this is a spreadsheet portion this is just a mock-up I'm doing for one of my schools so all of the data doesn't really pertain to any particular student and as you can see there's really no description of any incidents so we're gonna be talking about import range its function and then unique and sort and how to use those we're going to be using that with the full-year breakdown and I'm going to be importing all of the students here using import range and I'm going to be taking that information from here now import range can be done a couple different ways for example if I just create a new tab and on that tab I'm gonna just put that this is import range test so import range is pretty simple you start with an equal sign to do any formula in spreadsheets you have to start with an equal sign so that the spreadsheet knows you're about to ask it to use a function and then if you start typing import you can see you have different pieces here that you can use and we're going to be focusing on the import range piece so whenever you're importing a range you need two pieces you need where you're getting the information from and what piece of that information you want to take so the wear is going to be the URL and since we're importing from this particular piece the form response we're going to grab this and that's the URL and we're going to go back and we're gonna put it in here and it has to be surrounded by quotes so you can see there's the huge the big HTML code around quotes and then we're going to hit a comma so that's the first piece of information we're gonna get the information from this piece of the URL the second piece of the information is what are you gonna get so what we are going to get is we're going to get the information from form response so we're just going to copy this form response tab and then we're going to again using a quote put that in so now it knows what tab you're getting it from but you also have to tell it what column you want to take it from and to do that you need to use an exclamation point so in the explanation point signals the difference between your tab your column the first section of that is your tab and after the exclamation point is your columns that you're going to be pulling in so let's say I want to pull in all the information from B to P all the way down to the end of whatever it is so to do that I need to just put B colon P so now it knows that I want to go from column B 2 column P and take all the information in there so when I hit enter it's going to start loading and now it's going to ask me for permission even though it's the same spreadsheet you have to give it permission to get that data so you're going to do is click allow access and then it will populate all of it in this particular case we see that the headers are there and that we see all the information is there so now that we know how to set this up we're going to start talking about import range and it's used with the full year piece and the students so we have this formula here one of the things I want to talk about though before we do that is I want to talk about shortening this because sometimes on this URL right here this is a lot of information and it's a lot of information that can be screwed up and we're going to be using this particular piece over and over again and rewriting it every single time can be kind of daunting so what I'm going to do is I'm actually gonna copy this and I'm gonna put it in my variables tab that's over here so now that I have this URL copied I'm gonna go over to my variables tab which is right here and you're gonna see that I have a variable here so I'm literally just gonna paste that if I'm gonna just extend it a little bit just to make sure that I have all of it in the same piece and it's easier for me to see you can actually make several variables here you may have an original URL you may also have you know Student Services URL and you can put those all in here so instead of writing all the URLs or keeping them in track or keeping track of them you'll be able to point to these based on the location they are in the cells for this problem though all we're doing is we're dealing with the first one which a b2 so now instead of writing all of this information if I go in here and I delete out that URL all I have to do is click on my variables tab click on the p2 slot as you can see it says variables exclamation point b2 and as a reference it's asking for the tab and then what part of that tab you're looking for and if I hit enter it gives me the same data and this is a lot shorter to deal with then I had to deal with before and less of a chance of making a mistake so that's how you would use the variables tab to shorten out this code now this is going to be important for when we go to the full-year breakdown so I can actually take this information here and copy it and go to the full-year breakdown and paste it in here I'm gonna end up probably getting an error and the error says the results are not automatically expandable so the problem is is I have information in other spots here and it's not able to expand down in order to do all the information and that's because I'm taking so much I'm taking from B to P but if we're looking at the students name all I need to do is use column D so I'm gonna go and import my range from column D to to column D and I hit enter and it starts to load and then all of my students data will be there once it loads up you may see this error loading data it doesn't mean that you actually have an error it just means it's grabbing all the data that you had requested and it's taking a minute to load up so now that the data is loaded all I got to do is expand my columns to take all the information in I highlight the whole column I double click on there and it'll automatically expand for me so all of my students data is here and I did it through the import range but one of the things you're going to notice is that I have Smith Allen all of these names are kind of out of order and you'll notice that I have some students that are doubled up so the first thing I want to show you is that sort function that I was talking about and all I actually have to do is put the word sort in front and then put the import range in parentheses and which sort does it actually will alphabetize the names and as you can see as I hit enter all of the names are now alphabetized the second problem I have if you look at Allen Thomas they have three referrals and it's showing up three times and we don't necessarily want them to trick show up three times so now that I want to get rid of all three of these and only have one I need to use something called unique and all unique does is grabs unique identifiers and only gives those values back so I'm going to put the unique between the import range and the sort and if you notice every time I do that I have to open up a parentheses and that parentheses just segregates out which particular function you're using and the reason I need three of these parentheses is I'm using three functions sort unique and import range so now I hit enter and you can see that now I only have one of each individual student in here even though some of the students might have multiple multiple things are multiple infractions that they're being given a referral on one more thing I do want to mention is every time you open up a parenthesis you need to make sure you're closing a parenthesis so you'll notice that I also have three closed parenthesis here in conjunction with my three open parenthesis here this is how Google knows that you're done with that particular function so if you have any questions let me know via email or we watch the video

Channel: Ryan Goff

Views: 40,001

Keywords:

Id: VH24GlDwiqE

Length: 8min 16sec (496 seconds)

Published: Wed Feb 28 2018