Preparing Source Data

Quick reference

• 00:00 In this video, we're gonna look at the problem that occurs when we
• 00:05 have two separate tables and we want to feed a pivot table from them.
• 00:10 In this particular case, you can see that I have a Sales Transactions table with
• 00:15 some dates and quantities of my different sales items.
• 00:18 I also have a Inventory Items table,
• 00:20 which happens to have things like the sales price, the unit cost and the margin.
• 00:24 Now what I wanna do is try and pull these things together into one table,
• 00:28 because a pivot table native in Excel can only be based off of one table.
• 00:33 And the way that I'm gonna do that is I'm gonna try and deal with it using VLOOKUP,
• 00:37 which is everybody's favorite function and
• 00:39 the way that we can actually make this work.
• 00:41 So the first thing that I need to look at is which table am I going to try and
• 00:46 aggregate together?
• 00:48 And in this case, I can't pull values into this table, because there's multiple
• 00:52 different records for the same sales item, so that's not gonna work.
• 00:55 I need to lookup information from this table in this table and
• 01:00 bring these items back over to the right-hand side.
• 01:04 So the way I'm gonna do this, I'm gona say well, we've already got a date,
• 01:07 we've already got a SKU and I can see a SKU number and a brand over here.
• 01:10 Those both exist.
• 01:11 What I don't have is these guys, all the different types of quantities and
• 01:16 things like that.
• 01:17 So I'm gonna start off with type and we'll just add a new column to our table for
• 01:22 this and we're going to do a VLOOKUP.
• 01:24 So we'll =vlookup and we'll pick up the SKU, so that's what we want to lookup.
• 01:30 Where do we want to look it up over in this table over here.
• 01:34 And then we're going to say, give me the let me see, where is type.
• 01:39 This is the first, the second, the third column.
• 01:42 And of course, we always put comma false on our VLOOKUP to get an exact match.
• 01:45 We now have the different types.
• 01:48 I'd also like to pull over these three items, sales price, unit cost, and margin.
• 01:54 So we'll just copy that, right-click, and paste values to give us our new columns.
• 01:59 And again, we'll run back and quickly do a =vlookup(@SKU)
• 02:04 against the Inventory Items table, comma.
• 02:09 So for sales price, we're gonna go column one, two, three, four, five and
• 02:14 six, false.
• 02:18 We can see that that's pulled that back correctly.
• 02:20 The unit cost =vlookup.
• 02:23 Once again, the SKU number in the table that we're gonna lookup,
• 02:28 this one should be ,7,false, cuz it's the next column over from what
• 02:33 we just used and we can see Budweiser's pulled us back a cost of 24.4.
• 02:38 And finally, =vlookup to grab the margins.
• 02:42 We're gonna go lookup the skew in the table,
• 02:48 and pull back the 8, false.
• 02:52 So what we've done here is we aggregated the key information from two
• 02:56 tables into one, because now when I go and create my pivot table.
• 03:01 So equals, Insert > Pivot table and I'll use a new worksheet.
• 03:06 I can now grab my brand and throw it on rows and I can grab my type, maybe I'll
• 03:11 throw that right above it, just so I can see what's going on there, there we go.
• 03:16 And now, I can pull across my sales price into the values area and my unit cost and
• 03:21 my margin, except that I just realized there is a problem here.
• 03:25 It's pulling out the sum, but this doesn't look like my total sales when I
• 03:30 consider I had quantities of 64 units in some of these cases.
• 03:34 So if I run back and take a look here,
• 03:36 what it's doing is it's actually summing the total amount of times that sales
• 03:40 prices showed up in this particular column and that's not exactly what I wanna do.
• 03:45 So what I'm gonna need to do is I'm gonna need to add some more information to
• 03:49 my particular table here and what we're gonna do is we're gonna add total sales.
• 03:53 Now we can use some more advanced techniques for this, but sometimes simple
• 03:58 is better and all we're gonna do is say =(@(Sales @uantity) *(@Sales Price).
• 04:04 Here we go.
• 04:05 Let's do Total Cost and once again,
• 04:09 it's just =(@Sales Quantity) *(@Unit
• 04:14 Cost) and we'll also grab Total Margin.
• 04:19 And once again, =(@Sales Quantity) *(@Margin).
• 04:27 We can now go back to our pivot table, refresh the data and
• 04:31 you'll see that we get some new columns here.
• 04:34 So I'm gonna pull these guys off, cz I don't need them anymore and
• 04:38 we'll put Total Sales, Total Cost and Total Margin on our pivot table and
• 04:42 you can see that we're starting to get much more accurate results.
• 04:46 So this is the secret to working with pivot table.
• 04:48 Sometimes quick and easy is best way to deal with things.
• 04:51 We amalgamate our tables together and
• 04:53 we create the additional columns that we might need to sum things up quickly rather
• 04:57 than trying to work black magic with more advanced techniques.
• 05:01 Although, we'll certainly show those later on in the course as well.

Lesson notes are only available for subscribers.