Add 2 sheets of data to 1 Pivot Table

[Ramballah]

Good afternoon,

Today I have a very difficult problem which I cannot solve on my own.

I have 2 sheet's of data and I need them in one Pivot Table.
I already tested the method of Pivot Table and Pivot Chart Wizard which you'll find on google however I am not satisfied with the pivot table I get out of that.

My data looks as followed:
Sheet1 (Dollars)

In English its: Relation, Name, Dollars, Open Amount, Payment Date, Payment week, Payment year. In this order.

My second sheet (Constante Kosten):

In English its: Row, Relation, Name, Invoice NR, Open Amount, Invoice date, Payment date, Payment week, Payment year. In this order.



Now the reason they are on different sheets is because the sheet 'constante kosten' uses information from a different worksheet. And the sheet 'dollars' is manually filled by me or my co worker.

However we want both these sheets in one table.
It should look like this;
Name + payment week + payment year + open amount

However when I use the wizard table it comes out like this:

With this I cannot see the name of the relation. I cannot see which week got which amount (if you look at 01015 it just summed up the week numbers..)

So yeah I'm not sure if my problem can be solved. I also might be taking a wrong approach. So if you got anything please let me now every input is welcome!

Thanks in advance,
Ramballah

 

[sandy666]

I said update your profile (Account details) not post the excel version
there is Power Query and Pivot Table in use
so you need to check Data - Show Queries and source of the Pivot Table
next pivot
in front of each label you can see +/- so you can collapse or expand this

 

[Ramballah]

I said update your profile (Account details) not post the excel version
there is Power Query and Pivot Table in use
so you need to check Data - Show Queries and source of the Pivot Table
next pivot
in front of each label you can see +/- so you can collapse or expand this

Okay thanks alot. But now I notice.
That 178k on VDL and wegenbelasting is the 178k that should be in week 14 from the dollars. Look:

Now this week 14 needs to be at the Dollars and not at VDL and Wegenbelasting. They have their own respective week 14... Do you know what is causing this?

 

[sandy666]

I don't know, I just transform data but Pivot calculate numbers itself (in this case: SUM)
I didn't calculate anything manual
as I said: set pivot table as you wish because I don't understand your needs
or post layout you really want and don't change it from post to post

 

[Ramballah]

I don't know, I just transform data but Pivot calculate numbers itself (in this case: SUM)
I didn't calculate anything manual
as I said: set pivot table as you wish because I don't understand your needs
or post layout you really want and don't change it from post to post

The layout is perfect like this. So I have 3 vendors: VDL, Wegenbelasting and Dollars. Those are my 3 vendors and the vendor Dollars has its own sheet. I need week 14 from the vendor Dollars to be under the vendor Dollars and not under VDL or Wegenbelasting. VDL and Wegenbelasting have their own amount for week 14. Im trying to look things in the query but I barely understand how it works...

 

[Ramballah]

The layout is perfect like this. So I have 3 vendors: VDL, Wegenbelasting and Dollars. Those are my 3 vendors and the vendor Dollars has its own sheet. I need week 14 from the vendor Dollars to be under the vendor Dollars and not under VDL or Wegenbelasting. VDL and Wegenbelasting have their own amount for week 14. Im trying to look things in the query but I barely understand how it works...

So how it should look like in the end:

Is like this. But these are now 2 separate pivot tables and I need them to be one. (for a pivot chart later on)

 

[sandy666]

like this?

 

[Ramballah]

like this?
View attachment 8870

YES! Exactly that!!!!

 

[sandy666]

You've mixed column name with vendors
so try make them different (less mistakes in future)
and don't create tables/ranges with blank rows. that is why your file is almost 30 MB but mine is a few kb
third pivot
and again: update your profile about excel version

 

[Ramballah]

You've mixed column name with vendors
so try make them different (less mistakes in future)
and don't create tables/ranges with blank rows. that is why your file is almost 30 MB but mine is a few kb
third pivot
and again: update your profile about excel version

Thank you so much this is exactly what I wanted.
2 Questions though:
How exactly do I update my profile
How did you manage to do this (in case for the future if something like this happens again)

 

[Ramballah]

Thank you so much this is exactly what I wanted.
2 Questions though:
How exactly do I update my profile
How did you manage to do this (in case for the future if something like this happens again)

Oh I found out how you appended this. Now just the profile thing.