YTread Logo
YTread Logo

ImportRange with Sort and Unique Functions"},"lengthSeconds":"497","ownerProfileUrl":"http://www.you

Jun 09, 2021
We will use a mockup of one of our student referral systems that we have in place and this is a portion of the spreadsheet. This is just a mockup I'm making for one of my schools, so all the data there. It doesn't really belong to any particular student and as you can see, there's really no description of any incident, so we'll talk about the import range, its function and then

unique

and neat and how to use them. with the full year breakdown and I'm going to import all the students here using the import range and I'm going to take that information from here now the import range can be done a couple of different ways for example if I just create one new tab and in that tab I'll just put this is an import range test so import range is pretty simple it starts with an equal sign to do any formula in spreadsheets you have to start with an equal sign to let the spreadsheet know. you're about to ask it to use a function and then if you start typing import you can see that you have different pieces here that you can use and we'll focus on the import range part so whenever you import a range it needs two pieces , you need where you're getting the information from and what part of that information you want to take, so the use will be the URL and since we're importing from this particular piece, the form response We'll take this and that's the URL and we'll go back and we're going to put it here and it has to be in quotes so you can see there's the huge HTML code in quotes and then we're going to press a comma, so that's the first piece of information, we're going to get the information from this part of the URL, the second part of the information is what are you going to get, so what we're going to get is we're going to get the form response information, so we're just going to copy this form response tab and then again use a quote so that now know which tab you get it from but you also have to tell which column you want to take it from and to do that you need to use an exclamation point so at the explanation point tell the difference between your tab, your column, the first section is its tab and after the exclamation point are its columns. that you're going to enter, so let's say I want to enter all the information from B to P to the end of whatever, so to do that I just need to put B, a colon and P, so now you know I want to go from column B 2 column P and take all the information there, so when I press Enter it will start loading and now it will ask me for permission even though it is the same spreadsheet, you have to give it permission. to get that data, what you're going to do is click allow access and then everything will be completed.
importrange with sort and unique functions lengthseconds 497 ownerprofileurl http www you
In this particular case, we see that the headers are there and we see that all the information is there, so now that we know how to set this up, we're going to start talking about the import range and it's used with the full year and the students, so we have this formula here, one of the things I want to talk about, although before I do that is what I want to talk about. shorten this because sometimes in this URL here there's a lot of information and it's a lot of information that can get messed up and we're going to use this particular piece over and over again and rewriting it each time can be kind. very intimidating, so what I'm going to do is copy this and put it in my variables tab which is here, so now that I have this URL copied, I'm going to go to my variables tab. which is right here and you'll see I have a variable here, so I'm literally going to paste it if I'm going to spread it out a little bit to make sure I have it all in the same piece. and it's easier for me to see, you can actually create several variables here, you might have an original URL, you might also know the Student Services URL and you can put them all in here, so instead of typing all the URLs or keep track of them or by keeping track of them, you will be able to point them based on the location in the cells for this problem, although all we are doing is dealing with the first one, which is b2, so now instead of writing all this info, if I go in here and delete that URL, all I have to do is click on the variables tab, click on the p2 slot, as you can see it says the b2 variables exclamation point and for reference it requests the tab and then what part of that tab are you looking for and if I press enter it gives me the same data and this is much shorter to handle than what I had to handle before and there is less chance of making a mistake, this is how I would use the tab of variables to shorten this code.
importrange with sort and unique functions lengthseconds 497 ownerprofileurl http www you

More Interesting Facts About,

importrange with sort and unique functions lengthseconds 497 ownerprofileurl http www you...

Now this will be important for when we go to the full year breakdown, so I can take this information here and copy it, go to the full year breakdown and paste it. here I will probably end up getting an error and the error says that the results cannot be expanded automatically so the problem is that I have information in other places here and it cannot be expanded down to be able to show all the information and that is because I'm taking so much that 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'll import my range from column D to column D. and I press enter and it starts loading and then all my students data will be there once it loads you may see this error when loading the data it doesn't mean it actually has an error it just means it is capturing all the data you had it requested and it's taking a minute to load, so now that the data is loaded all I have to do is expand my columns to take all the information.
importrange with sort and unique functions lengthseconds 497 ownerprofileurl http www you
I highlight the entire column. I double click there and it will automatically expand. so all my student data is here and I did it through the import range, but one of the things you'll notice is that I have Smith Allen, all of these names are a little bit out of order and you'll notice that I have some students that are duplicates , so the first thing I want to show you is the

sort

function that I was talking about and all I have to do is put the word

sort

in front and then put the import range in parentheses and which to sort, it will actually alphabetize the names and as you can see when I press Enter all the names are now sorted alphabetically.
importrange with sort and unique functions lengthseconds 497 ownerprofileurl http www you
The second problem I have, if you look at Allen Thomas, they have three references and it appears three times and we don't do it. necessarily I want them to appear three times, so now that I want to get rid of all three and I only have one, I need to use something called

unique

and the only thing it does is take unique identifiers and it only returns those values, so I'm going to put the unique value between the import range and the type and if you notice that every time I do that I have to open a parenthesis and that parenthesis just segregates which particular function you are using and the reason why I need three of these. parentheses is that I'm using three

functions

sort unique and import range so now I press Enter and you can see that now I only have one of each individual student here, although some of the students may have multiple things, they are multiple violations that are giving me a reference about one more thing I want to mention is that every time you open a parenthesis you need to make sure you close it, so you'll notice that I also have three closed parentheses here along with My three open parentheses here, that's how Google knows you're done with that feature in particular, so if you have any questions, let me know by email or let's watch the video.

If you have any copyright issue, please Contact