I need some help with finding complex information I want to know about a sales document with many rows.
The table has 3 columns; customer number, product description and date purchased.
I put an example of the different kinds of results there are.
The management wants to know in how many cases a customer ordered more than one kind of product on the same date. They want to know about the customers from 1 april till 1 september.
So for example if customer X placed 2 orders on 1 June and 1 order is a table and 1 order is chair than this is a customer I want to see in my pivot table.
I want to calculate how many cases fit this description. If a customer placed 3 orders on 1 June but the 3 orders are all the same product than I doesn't match the description and I need to filter it out.
If also need to filter the orders before 1 april. So if the order was placed at 1 February it doesn't count.
I want to see the customer number and product descriptions at the end. So the management can see exactly about what customers and products it's about.
Because the date purchased ranges from the first of the month till the last I filterd the date in the pivot table at days.
So far I found out in how many cases a customer placed multiple orders on the same date. The pic below shows what I did. It is in dutch, sorry for that.
The original table is a very big document with 53k rows. Because of that I can't drag customer number or product description to the column section of the pivot table.
There are too many of them and I get an error and Excel crashes.
Another problem I faced is that filters doen't work properly. In the row label filter I filtered on bigger than 1 value. That works.
I changed the pivot table settings to allow multiple filters. But I can't filter the column label filter to bigger than 1 value. It should filter out the 1s but they still are in the pivot table.
It works on my example above but not in the big pivot table I am working with. So I used =COUNTIF(B5:H5;">1") after each row. Then I tried to use =SUM function but it doenst work in my document. So I found out I had to use =SUM(--(J5:J8)). That gave me the amount of customers with more than one order on the same date.
But now I need to figure out how many times a customer placed multiple orders on the same date with containts different products.
In my example you can easily see that onlye customer 10000 has placed multiple orders on one date which containts two different kind of products.
But in my massive pivot table this way is useless because it is way too much info.
The management wants to know the exact number and the exact products. I don't know how to do this with so many info.
Maybe I am facing this the wrong way but I am stuck.
If anybody could help me out or give me some tips that would be great!
The table has 3 columns; customer number, product description and date purchased.
I put an example of the different kinds of results there are.
The management wants to know in how many cases a customer ordered more than one kind of product on the same date. They want to know about the customers from 1 april till 1 september.
So for example if customer X placed 2 orders on 1 June and 1 order is a table and 1 order is chair than this is a customer I want to see in my pivot table.
I want to calculate how many cases fit this description. If a customer placed 3 orders on 1 June but the 3 orders are all the same product than I doesn't match the description and I need to filter it out.
If also need to filter the orders before 1 april. So if the order was placed at 1 February it doesn't count.
I want to see the customer number and product descriptions at the end. So the management can see exactly about what customers and products it's about.
Because the date purchased ranges from the first of the month till the last I filterd the date in the pivot table at days.
So far I found out in how many cases a customer placed multiple orders on the same date. The pic below shows what I did. It is in dutch, sorry for that.
The original table is a very big document with 53k rows. Because of that I can't drag customer number or product description to the column section of the pivot table.
There are too many of them and I get an error and Excel crashes.
Another problem I faced is that filters doen't work properly. In the row label filter I filtered on bigger than 1 value. That works.
I changed the pivot table settings to allow multiple filters. But I can't filter the column label filter to bigger than 1 value. It should filter out the 1s but they still are in the pivot table.
It works on my example above but not in the big pivot table I am working with. So I used =COUNTIF(B5:H5;">1") after each row. Then I tried to use =SUM function but it doenst work in my document. So I found out I had to use =SUM(--(J5:J8)). That gave me the amount of customers with more than one order on the same date.
But now I need to figure out how many times a customer placed multiple orders on the same date with containts different products.
In my example you can easily see that onlye customer 10000 has placed multiple orders on one date which containts two different kind of products.
But in my massive pivot table this way is useless because it is way too much info.
The management wants to know the exact number and the exact products. I don't know how to do this with so many info.
Maybe I am facing this the wrong way but I am stuck.
If anybody could help me out or give me some tips that would be great!
