Pivot table - Can't figure it out - Complex info

sjerpeli

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
Voorbeeld tabel.png

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.
Draaitabel start.png

Draaitabel index.png

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.
tweede voorbeeld.png

derde voorbeeld.png


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!
 

Attachments

  • Draaitabel start.png
    Draaitabel start.png
    6.2 KB · Views: 2
  • 1599228766300.png
    1599228766300.png
    19.4 KB · Views: 2

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
Not sure if this will work but in your original data set add a helper column (presumably in col D) with formula

=COUNTIFS(A:A,A2,C:C,C2,B:B,"<>"&B2) and copy down

This should identify any multiple purchases on the same date where the products are different

You can then pivot the data with

Row labels
Customer Number
Product Description

Column Labels
Date

Value
Max of colD (ie the helper column you've created)

Report Filter
colD excluding anything with a value of o
 

sjerpeli

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Jim,

Thank you so much for your message!
I tried your formula. I think you meant ; instead of , to make it work.
=COUNTINFS(A:A;A27;K:K;K27;C:C;"<>"&C27)
1599263295290.png

The first column is the customer, the second is the product description (a number in my case) and the third is the date
I think it worked but I have some questions. I dont really get why it shows a 2 on nr 20004903 and a 1 on 20006354.

How can I calculate the total amount of more then 1 kind of product on the same date? Do I need to that in the pivot table?
For example the pivot table looks like this now.
1599264035761.png

But when I press on the 3 and the 1 I get this:
1599264110410.png

1599264125896.png

Aankoop is the helper column name.
The pivot table has 3000 rows but I need to know the total first.
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
The formula should have worked with the ,
Not sure if it's something to do with the dutch conversion?

It shows a 2 on rows 35 & 36 as there has been an order for 2 of the same product on the same day, as well as another product on the same day
The same applies to rows 37 & 38

On rows 39 & 40 there has been orders for 2 different products on the same day but only 1 of each
 

sjerpeli

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Jim, thanks for your answer. I think I understand what you mean.
But it seems like the results are wrong.
For example:
1599462516671.png

It should be the other way around right? Do you know how I can fix this?
Row 342 should be 1 and 340 and 341 should be 2.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,308
Members
410,545
Latest member
Upsindustrial20
Top