Because Alex's client trades in many different regions, they actually need up to date currency exchange rates in their Excel workbook. Now Alex has achieved this by linking the workbook to a website that has current currency exchange rate information. The only problem is they also want them to be sorted and every time they come and refresh the data, they lose the sort order and they don't want to have to manually re-sort it. So, we have two things we're going to look at in this video. The first one is how Alex achieved this linking the data and the second one is how we can use an Excel function to sort data. Right, let's start by removing this data, so I'm going to select all the values except the currency codes and I'm going to press delete and it's checking if I want to remove the link which I do. So, we going to say yes. Now before you can link to some data on the website you must obviously have the website address or the URL and in this case we've made your life easy by popping into the workbook. So, just right click on this, you don't accidentally follow the link which is what I've just done. Just right click on that and then you can left click and Ctrl+C. Now we're going to click into the cell where you want the imported data to begin and we're going to come to our data tab. If you're working with any version of Excel other than 365, you will see an option somewhere here to import data from web, please use that. Office 365 they've actually updated this function. And so that we're all doing the same thing we going use the legacy wizard. So, we're going to come to get data, legacy wizards and from web, but that's just for Office 365. When you click on that it will automatically go to your browser home page and if there were scripts on that page you might get some errors. So, just click no until the errors go away. Then come into the address bar and paste in your URL and click go. And again if there are errors on the web page you will get these errors popping up. So we're just going to click no until they go away. Now in order to link to data on the web that data must be stored in a table. But you'll find that generally things like currency exchange rates price lists are in tables and you can spot the tables because they've got little yellow arrows next to them. And to select one click on the yellow arrow and then click Import and say okay. And in a moment that live data will actually be loaded into your web book. Now it won't automatically refresh but you can right click on the data at any point and choose the refresh option or if you want to you can come to the data range properties. And you can actually specify, I'll just move that up, you can actually specify that you wanted to automatically refresh every hour or every five minutes, but bear in mind that that may impact the performance of your workbook. We're going to leave it at this and say okay. So there's our data but we have lost our sort order. So, the next step is to look at how we can use a function to sort the data as it comes through but in another worksheet. So, we're going to come to our Conversion table and these values at the moment are just being pulled through from the other workbook. But we want add a formula that will pull them through but sort them in the process. We're going to do this in two steps. The first step is we're going to rank all the currency codes. Now unfortunately we can't use the rank function because that only works with numbers, but the good news is Excel does recognize an A as being smaller than a B. So we can still rank the text and we're going to do this using a COUNTIFS function. We're going to start in N5 and type =COUNT and choose COUNTIFS. And then my criteria range is in my other sheets, so I'm going to come back to Current Rates and I'm going to select these values. Now what I really want to do is check how many of the values in this list are smaller than Euro, so come before Euro. So, for example if I were to check Australian dollar the number that smaller is actually zero. So, rather than using less then I'm going to use less than or equal to. So my criteria, I'm just going to type a comma. My criteria and I'm in the formula bar if you're following, is going to be "less than or equal to". Just remember with COUNTIFS and SUMIFS, if you using logical operators they must go in quotes. Then my & to join that to my cell reference and I'm going to click Euro, close my brackets and I'm expecting to get a five because it's ranked 5 in the list and there it is. And if I copy that down to row 14, there are all my codes ranked and you can see that the Australian dollar is ranked highest in the list because it comes first alphabetically. So, that was my first problem solved. Now the second part of this is what I want to do is identify for each row I want to match it to its correct ranking. So, if it's row one I want to get the first ranked item. If it's row two, I want to get the second ranked item. But before I can do that I need to work out which row I'm in, and to do this we can use Excel's row function. So, in A5 we're going type =ROW and that is going to turn me to row five which is not actually what I want, I want row one. So I'm going to just subtract four. And then if we copy that down, I've now got my row numbers. So, that's step one. Now the next step is I want to work out where in the list the first item is actually positioned? So, I can see it is positioned in the fourth position and to do that I'm going to use my match function. So, coming back to here, I'm going to double click in A5 and just before the row, I'm going to type M for MATCH and my lookup value is going to be my row numbers, so that's perfect just type a comma after the four and my lookup array is my little array over here. And then don't forget to put your dollars on which you are going to copy this down. And my MATCH is an exact match, so type zero, close your bracket and Enter. So what that's saying is the value I want to come in my first position at the moment is in the fourth position. And when I copy that down, I can now see that it's putting all the right values in the right position, but I don't want the numbers I want the actual value. So, the last step is to throw an index into the mix and what the index will do is go back to my list of currency codes and get back the fourth one the fifth one etc. So, let's put our final cherry on the cake. I'm just going to slip a little index into that and my array is going to be this rates array over here and then type your comma. And the actual lookup is going to be that value that we've just worked out, close your brackets and Enter. There's my Australian dollar and if I copy that down I now have all my currency codes coming through sorted. So, it doesn't matter what order the data gets imported in, when I come into my actual conversion table I will have it in the right order. So, some absolutely amazing things you can do with Excel functions and we've just seen how we can sort data using the INDEX, MATCH, ROW and COUNTIFS functions. And in the next video, we're going to start looking at an even more exciting techniques, so keep watching.
