Can you use a pivot table to measure how many unique combinations of customer and product appear in a data set? Well, you can't do it natively, but in Episode 758, we take a look at a formula such as =IF(SUMPRODUCT(($B$2:$B2=B2)*($D$2:$D2=D2)) 1,0,1) which will allow the calculation to work. Also - a look at the Amazon Kindle.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Another question from seminar, someone said, "Hey, how can I use a pivot table to count the unique occurrences have something in." That's hard to do when a pivot table if we do data, pivot table, click [ finish ].
Let's say that we put customer up here in the page area, products going down the row fields, and then I'll take any field for example, revenue and put it in data items.
And I want to change that from a SUM to a COUNT.
So, I double-click the heading called "Sum of revenue", change it to a count, click [ ok ].
Well, it says, 203 orders for ABC, but that doesn't mean 203 customers bought ABC.
It might mean that one customer bought ABC, 203 times.
What we're trying to find out, is how many customers bought ABC?
And so what we're going to do is go back to the original data set and add a brand new formula.
This formula is going to use the sum product and it's cleverly written and that it uses a mix of absolute and mixed references.
So, here at the beginning we're interested in every unique combination of product and customer.
Products are in B, customers are in D.
We are going to multiply two different terms together and each one is written dollar sign B, dollar sign 2 that says, we're always going to start at row 2 but then go down to dollar sign B2.
In other words, the current row.
Let me show you a little bit lower how this works, so here.
We're still starting at B2 but it's checking everything from B2 to B7, to see if it's equal to B7.
Now, that's going to give me an array of trues and falses.
We're also going to do the same thing for D2 to D7, to see if it's equal to D7.
Again, we get true or false, is and we have to multiply those two terms together.
When you multiply trues and falses, you coerce Excel into giving you an array of ones and zeros.
True times true is one, anything else is 0.
So, we check to see if that number is greater than one if it is, then it give us a zero out here in the unique column and what we get you'll see that initially almost everything is one.
First time this customer order this product, but here is one example where general motors has ordered ABC, twice.
But if I scroll down through the spreadsheet, we get to the point where almost every customer, product has already been selected.
Every once in a while, you get to something.
Here is Philip Morris, buying DEF for the first time.
So, now if we create a pivot table from this data set with that extra field, data, pivot table, click [ finish ].
We'll put product down the left-hand side, customer up in the page area and this time use the unique field.
Now, I can see that XYZ was bought by 11 customers, DEF by 25 customers and ABC by 27 customers.
Now, if I want to see which customers purchase that, of course I just drop customer on the right side of column A and I can get a list of who the 27, the 25 and the 11 customers are.
Well, there you have it.
Something that sounds easy getting a unique count in a pivot table, but actually requires a fairly difficult formula.
I'll put that formula in the blogger note.
So, you can take a look at it in the blog page for this podcast.
Well, if you've been watching the podcast for a while, you know that every once in a while, I'll promote a book.
I'll say hey you know, if you need to know more about pivot tables.
Check out pivot table Data crunching.
Today, I want to do something very unique.
I want to talk about pivot table data crunching, which is now available on the Amazon Kindle.
This is finally in stock.
We were able to get one, last week.
I have to tell you, I love it.
Great little e-book reader, is in black and white.
But you can just fly right through and it has a wireless interface doesn't require Wi-Fi in the office.
It uses a cell phone signal just an amazing, amazing little reader there and my three latest books on for QR out.
We're getting all the holy macro books and we're getting them available on the kindle as soon as possible.
So this is a just, an amazing little reader if you travel a lot and you like to read.
This is a great way to go and check out pivot table Data crunching on the kindle.
Thanks for stopping by, we'll see you next time for another netcast for MrExcel.
I'm Bill Jelen.
Another question from seminar, someone said, "Hey, how can I use a pivot table to count the unique occurrences have something in." That's hard to do when a pivot table if we do data, pivot table, click [ finish ].
Let's say that we put customer up here in the page area, products going down the row fields, and then I'll take any field for example, revenue and put it in data items.
And I want to change that from a SUM to a COUNT.
So, I double-click the heading called "Sum of revenue", change it to a count, click [ ok ].
Well, it says, 203 orders for ABC, but that doesn't mean 203 customers bought ABC.
It might mean that one customer bought ABC, 203 times.
What we're trying to find out, is how many customers bought ABC?
And so what we're going to do is go back to the original data set and add a brand new formula.
This formula is going to use the sum product and it's cleverly written and that it uses a mix of absolute and mixed references.
So, here at the beginning we're interested in every unique combination of product and customer.
Products are in B, customers are in D.
We are going to multiply two different terms together and each one is written dollar sign B, dollar sign 2 that says, we're always going to start at row 2 but then go down to dollar sign B2.
In other words, the current row.
Let me show you a little bit lower how this works, so here.
We're still starting at B2 but it's checking everything from B2 to B7, to see if it's equal to B7.
Now, that's going to give me an array of trues and falses.
We're also going to do the same thing for D2 to D7, to see if it's equal to D7.
Again, we get true or false, is and we have to multiply those two terms together.
When you multiply trues and falses, you coerce Excel into giving you an array of ones and zeros.
True times true is one, anything else is 0.
So, we check to see if that number is greater than one if it is, then it give us a zero out here in the unique column and what we get you'll see that initially almost everything is one.
First time this customer order this product, but here is one example where general motors has ordered ABC, twice.
But if I scroll down through the spreadsheet, we get to the point where almost every customer, product has already been selected.
Every once in a while, you get to something.
Here is Philip Morris, buying DEF for the first time.
So, now if we create a pivot table from this data set with that extra field, data, pivot table, click [ finish ].
We'll put product down the left-hand side, customer up in the page area and this time use the unique field.
Now, I can see that XYZ was bought by 11 customers, DEF by 25 customers and ABC by 27 customers.
Now, if I want to see which customers purchase that, of course I just drop customer on the right side of column A and I can get a list of who the 27, the 25 and the 11 customers are.
Well, there you have it.
Something that sounds easy getting a unique count in a pivot table, but actually requires a fairly difficult formula.
I'll put that formula in the blogger note.
So, you can take a look at it in the blog page for this podcast.
Well, if you've been watching the podcast for a while, you know that every once in a while, I'll promote a book.
I'll say hey you know, if you need to know more about pivot tables.
Check out pivot table Data crunching.
Today, I want to do something very unique.
I want to talk about pivot table data crunching, which is now available on the Amazon Kindle.
This is finally in stock.
We were able to get one, last week.
I have to tell you, I love it.
Great little e-book reader, is in black and white.
But you can just fly right through and it has a wireless interface doesn't require Wi-Fi in the office.
It uses a cell phone signal just an amazing, amazing little reader there and my three latest books on for QR out.
We're getting all the holy macro books and we're getting them available on the kindle as soon as possible.
So this is a just, an amazing little reader if you travel a lot and you like to read.
This is a great way to go and check out pivot table Data crunching on the kindle.
Thanks for stopping by, we'll see you next time for another netcast for MrExcel.
