 Hello everyone and welcome back to another tutorial. In this video we're going to be looking at a problem and then trying to or giving you a solution on how you can get around that So the problem is going to be that we've got two sets of data The first one we can see we've got the country city and the units and it might help to explain We're talking about like volume of sales So units is so that the number of items that were sold and then total sales would be obviously what that the Sale of those units generated in obviously monetary value. So obviously if something's worth Simple one down here two units are sold and obviously each unit was worth 10 pounds. Therefore, obviously total sales is going to be 20 pounds So the scenario is we've got this table here and we can see the number of items that were sold So units but we want to see what the total sales were and we then also want to get the average sale So obviously what the average sale is so the total sales divided by the units The problem is that our sales data is in a separate sheet in a separate tab So we need to bring that into that that first sheet so that we can obviously have the total sales then do that division The problem comes because we want to pull this information from sales data into the unit sold But we don't have a unique identifier in order to do our V lookup So unique identifier would be a number or reference that would be unique to each City or site What would allow us to obviously do that comparison to find the values and then return it into that that first sheet so what we're going to have to do is create a custom key that allows us to Initially create our own unique identifier so that we're able to reference That in our in our unit sold So I'll step through and hopefully the the situation will become more clear and hopefully then become more relatable to Situations that you might have had before or you might find yourself in the future and then hopefully this will give you the solution for that What we're first going to do is we want to pull sales data into the unit sold So we first need to enter our key on this here. So I'm just going to enter a New column at the beginning of our data because we're going to be using the V lookup So it needs to be at the front You can obviously put this key anywhere and then use another function like index match do it But to keep it more straightforward. We're going to just be using the V lookup function so in order to create a key we need to Combine some unique characteristics of each of our sites To form this unique key for this example is quite simple there's only each city is only going to appear on here once and For each city you're going to have the country and that city name So we're going to combine these two fields together to give our unique key if you had other Fields in here or you had maybe like the multiple city or the city wasn't here twice because it's got two different Office addresses or whatever it may be then this is where you then obviously need to include additional fields If you've got them available to allow you to create that unique identifier Obviously the one thing you can't do is obviously use the total sales Because if you use the total sales It's not going to be something that we can compare in our other sheet and we'll get get to that in a second But ultimately we need to combine fields to create our key From fields that are going to be present in both data sets is probably a better way to put it What we'll do is do our equal sign and can that make concatenate the two together So do equals and we'll select this the value in column B. I mean, we're doing ampersand Quotation and underscore another quotation and ampersand and then the last piece of the key the city and hit enter And you can see how that key looks You don't really need to worry too much when putting two values together But this is like a general practice I'd use especially if you've got your longer keys it really helps obviously about a To actually about to look at that key and distinguish what location is referring to Rather than just bunging everything together and you've got a long string of words or letters even Once done that we can just copy that down and you can see that everyone has now got a unique identifier So if we were to look at say France We can see that we've got obviously France and all these different sites and you can see we've got each one Refers to it's your own unique location So we can easy now do a V look up on France underscore Nancy and then pull back this total sales of 4 on 4 When we then go to our unit sold We don't need to worry about entering a key here because what we're going to do is embed that key within our V look up But first we'll enter the key. So we're going to firstly we do equals set the country ampersand Cretation underscore quotation ampersand and then collect our city so we can see what our key looks like So now that we've got a key, we just wrap around that the V look up. So we want to V look up Open brackets and the lookup value is obviously this straight this formula if you want to call it out concatenation We was put together of our key We'll do a common just to go into our table array So now we need to go into our sales data and make sure our key is the first Column within that data and we'll just go over these four columns And we want to return the sales that a total sales was available in column 4. So go comma 4 comma and I'm going to do a zero because we want to get an exact match for this Close brackets and enter and you can see that value has pulled through for us because it was able to combine these together To find United States Austin and then let's locate that within the key for now copy that down You can see that for all of our Results it's now found the value what coincides with it great So we've got no errors So that's all work perfectly fine for us And this is where we've been able to use our key and obviously get source our data So this is kind of the main point of the video really if you don't have a unique identifier You can construct your own unique identifier by combining Distinguishing fields from each site together into one string that allows you to look up based on that value And then to the average sales, this is a really easy bit All we now need to do is take doing equals and we'll take our total sales divided by our unit sold Hit enter Let me copy this down and now all we can simply see here is obviously what was the highest average sales? So we can see in Where's a good example? Let's look here. We've got in Charlotte. We can see yes great They had obviously 300 pounds worth of sales But that was just obviously a lot of sales lots of small smells at five pounds or you've got ones This one's done quite well as well. What total sales are 1600 here or the alternative is you've got one here They didn't have many sales so you might say rather than something saying are you've done not done many sales You're not done really good this month, but actually obviously total sales 250 pounds So they were actually focused more on selling maybe the high-ticket items rather than lots of small stuff But that's by the bar and we're going into it to debt in depth now It's just an example to show you how you can put this together and like I say It's just showing you how you can construct your own unique identifier When it's not present in your data, so hope that made sense more importantly and you've had either It's now giving you a solution to a problem You might currently have or if you come across this in the future again You've got something in your pocket you can now refer to So you can solve that potential issue if you haven't already please do subscribe to the channel I've got lots more videos coming out. We do at least once a week But when we can we'll try and get more than one out each week as well If you do have any questions, just drop us a comment below this video And we'll get back to you as soon as possible and lastly if you did enjoy the video Please do give it a like not only does it really help me out and shows me the content that you'd like to see more Of and you find most useful, but of course It's also going to help that YouTube algorithm to make sure that we can get out to more people and help them with these Excel Problems so once again, thank you very much for watching and we shall see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlist so you can see these categorized together So make sure you check those out and get all those useful information and obviously as always Don't forget to subscribe and hit that bell notification button