Touma sends in what should be an easy question. Sheet1 contains customer, item, and quantity sold. Sheet2 is a price list with unique prices per customer name. I could have solved this with PowerPivot or with a two-way lookup. As I started to do the lookup, though, I discovered that the sales database include a lot of items that are not in the pricing table. Some are simple misspellings, but others are simply missing. This is representative of real life - the pricing manager's data doesn't match the sales data.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1733 - Price Table Lookup with Bad Data!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question sent in by Touma. Touma has a dataset, it's trying to create a Pivot table that will calculate total revenue, but the data is on two different sheets. So here we have customer name, the item, and a quantity, and then there is a second sheet called Price List, that has a customer name, the item going across, and then the price. See, everyone has their own price, so for some reason Wanda Hill is getting a better price than Billy Arnold. Maybe it's based on the volume that they bought last year, who knows. But you know, we need to merge these two, and I was going to try and go high-tech with PowerPivot, but let's just do a plain old LOOKUP here. And so the first thing I want to do is, I want to figure out which row that customer is in, so =MATCH of this customer name, comma, I'll click on the price list tab, and we will highlight that range of customers. Press F4, ,0 , because we want an exact match, and that tells us that Teresa is in row 1, double click to shoot that down. And now for every customer we know where in that price table their prices are, and then which column, alright.
MATCH is great, it goes either vertical like VLOOKUP, or horizontal like HLOOKUP. So we're looking up cement, comma, over here in the price list, and this time it's going to be across the top. So I turn those cells, F4, ,0 for an exact match. Alright, so we know it's in column 6, double click to copy that down. Oh, I'm getting a whole bunch of #N/A's, brick 10cm, I don't know, this is really annoying.
So the price list has different product names going across the top than what's in the data, how did that ever happen? So you know, this is typical, this happens in real life all the time, some pricing manager created this, and didn't use the real item timers across the top. Alright, so let's figure out what our unique list of item numbers are. Ctrl+C to get the whole column coming over here, Ctrl+V back on the data tab, remove duplicates, click OK. Alright, there's 11 unique items and I know that that other price list is not that wide.
OK so, we're going to Ctrl+C in this, take it over a priceless sheet, and we'll start to fill some things in. So brick 10cm, actually I'm going to cut, that way I know that I've finished that one. And then brick 15, and then brick 20 there, brick 14 and brick 18 don't even exist, cement is there, that was the one that was working. So we don't have any prices for gravel, sand, boudra, hordile 14, hordile 18, hordile 24. Alright, so we're going to have to go back to the pricing manager and get them to fill this in. Looks like we need 6 extra columns, and we will copy Ctrl+C up here, and Alt E S E for Paste Special Transpose, and now someone's going to have to fill in these prices. And what I'm going to do, this is horrible, I'm just going to use =RANDBETWEEN(70,100)/100, just to put some prices in. So that way we can keep going here, but in real life we're going to have to come back to the sales manager, and get the sales manager to fill in all of those prices. Because otherwise, you know, if you don't have the right data, there's no way you're we're going to get the correct information.
So now we're looking from C2 to end, let's see if that's still correct, the price table goes from C2, and that's perfect. Alright so, now what that's telling us is which row and which column the actual price is in. Alright so, to get the revenue, you know what, I'm going to do it in two steps, what the heck.
So the price is going to be =INDEX of our price table, and we'll start with the very first data. I will include the headings, press F4, comma, and then which row is that answer, and which column is that answer. Notice that they put the sheet names(?) in, even though it does not need to be. So let's get rid of that, that's just F2 and G2. Alright, double click to copy that down. And then, I would call this extended price or revenue, let's call it revenue, is equal to the unit price, times the quantity. Alright, so this would have been a really quick podcast had the item names here match what was in the price list.
We could have just been done very quickly.
Now to get the totals by customer, we could either use subtotals, which would mean we'd have to sort, or a Pivot table. Let's do a Pivot table, Insert, PivotTable. OK, we'll put, let's make this bigger here, we'll put names down the left hand side, and then check-mark revenue. And we now have a report for every single customer, what their revenue was for the whole year. OK well yeah, a less-than-perfect situation, but in real life that happens, right? Our data doesn't add up. And so you have to take this extra step and see, you know, all of the unique items. So we did this with the Remove Duplicates, and then put them in the price table, and in our case we've got a whole bunch of items we sold that weren't even in the price table. So you have to go back to your sales manager and get that to be filled in.
I want to thank Touma for sending that question in, I want to thank you for stopping by, we'll see you next time in another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1733 - Price Table Lookup with Bad Data!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question sent in by Touma. Touma has a dataset, it's trying to create a Pivot table that will calculate total revenue, but the data is on two different sheets. So here we have customer name, the item, and a quantity, and then there is a second sheet called Price List, that has a customer name, the item going across, and then the price. See, everyone has their own price, so for some reason Wanda Hill is getting a better price than Billy Arnold. Maybe it's based on the volume that they bought last year, who knows. But you know, we need to merge these two, and I was going to try and go high-tech with PowerPivot, but let's just do a plain old LOOKUP here. And so the first thing I want to do is, I want to figure out which row that customer is in, so =MATCH of this customer name, comma, I'll click on the price list tab, and we will highlight that range of customers. Press F4, ,0 , because we want an exact match, and that tells us that Teresa is in row 1, double click to shoot that down. And now for every customer we know where in that price table their prices are, and then which column, alright.
MATCH is great, it goes either vertical like VLOOKUP, or horizontal like HLOOKUP. So we're looking up cement, comma, over here in the price list, and this time it's going to be across the top. So I turn those cells, F4, ,0 for an exact match. Alright, so we know it's in column 6, double click to copy that down. Oh, I'm getting a whole bunch of #N/A's, brick 10cm, I don't know, this is really annoying.
So the price list has different product names going across the top than what's in the data, how did that ever happen? So you know, this is typical, this happens in real life all the time, some pricing manager created this, and didn't use the real item timers across the top. Alright, so let's figure out what our unique list of item numbers are. Ctrl+C to get the whole column coming over here, Ctrl+V back on the data tab, remove duplicates, click OK. Alright, there's 11 unique items and I know that that other price list is not that wide.
OK so, we're going to Ctrl+C in this, take it over a priceless sheet, and we'll start to fill some things in. So brick 10cm, actually I'm going to cut, that way I know that I've finished that one. And then brick 15, and then brick 20 there, brick 14 and brick 18 don't even exist, cement is there, that was the one that was working. So we don't have any prices for gravel, sand, boudra, hordile 14, hordile 18, hordile 24. Alright, so we're going to have to go back to the pricing manager and get them to fill this in. Looks like we need 6 extra columns, and we will copy Ctrl+C up here, and Alt E S E for Paste Special Transpose, and now someone's going to have to fill in these prices. And what I'm going to do, this is horrible, I'm just going to use =RANDBETWEEN(70,100)/100, just to put some prices in. So that way we can keep going here, but in real life we're going to have to come back to the sales manager, and get the sales manager to fill in all of those prices. Because otherwise, you know, if you don't have the right data, there's no way you're we're going to get the correct information.
So now we're looking from C2 to end, let's see if that's still correct, the price table goes from C2, and that's perfect. Alright so, now what that's telling us is which row and which column the actual price is in. Alright so, to get the revenue, you know what, I'm going to do it in two steps, what the heck.
So the price is going to be =INDEX of our price table, and we'll start with the very first data. I will include the headings, press F4, comma, and then which row is that answer, and which column is that answer. Notice that they put the sheet names(?) in, even though it does not need to be. So let's get rid of that, that's just F2 and G2. Alright, double click to copy that down. And then, I would call this extended price or revenue, let's call it revenue, is equal to the unit price, times the quantity. Alright, so this would have been a really quick podcast had the item names here match what was in the price list.
We could have just been done very quickly.
Now to get the totals by customer, we could either use subtotals, which would mean we'd have to sort, or a Pivot table. Let's do a Pivot table, Insert, PivotTable. OK, we'll put, let's make this bigger here, we'll put names down the left hand side, and then check-mark revenue. And we now have a report for every single customer, what their revenue was for the whole year. OK well yeah, a less-than-perfect situation, but in real life that happens, right? Our data doesn't add up. And so you have to take this extra step and see, you know, all of the unique items. So we did this with the Remove Duplicates, and then put them in the price table, and in our case we've got a whole bunch of items we sold that weren't even in the price table. So you have to go back to your sales manager and get that to be filled in.
I want to thank Touma for sending that question in, I want to thank you for stopping by, we'll see you next time in another netcast from MrExcel!
