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

 

[mole999]

If I were doing two table in excel I would need the same number of columns and headings to be in the same place with values that would be comparable, i.e text or values. I will be watching as I have never joined two tables for one pivot

 

[Ramballah]

If I were doing two table in excel I would need the same number of columns and headings to be in the same place with values that would be comparable, i.e text or values. I will be watching as I have never joined two tables for one pivot

I Have uploaded the workbook to easyupload.io so you can copy the information and test!
easyupload.io

 

[sandy666]

what is VDL holding bv and Vegenbelasting/Vegverkeer ?
there is no something like that in your source tables

edit: ok, I found it ?

like this?

Row Labels	Sum of Openstaand bedrag
VDL holding bv	178643.68
2020	178643.68
14	178643.68
Wegenbelasting / Wegverkeer	178643.68
2020	178643.68
14	178643.68
Grand Total	357287.36

 

[Ramballah]

what is VDL holding bv and Vegenbelasting/Vegverkeer ?
there is no something like that in your source tables

edit: ok, I found it ?

like this?

Row Labels	Sum of Openstaand bedrag
VDL holding bv	178643.68
2020	178643.68
14	178643.68
Wegenbelasting / Wegverkeer	178643.68
2020	178643.68
14	178643.68
Grand Total	357287.36

Yes but with the dollars in there aswell. Dollars have many different weeks and I want to see the sums of the different weeks.

 

[sandy666]

but you didn't show where the dollars column should be
it should be a sum or just as labels ?

 

[Ramballah]

but you didn't show where the dollars column should be
it should be a sum or just as labels ?

The dollar amounts I do not need. I need the same columns as you use with VDL and Wegverkeer. So I need the sum of 'Openstaand bedrag' That dollar column is just for intern. But we need that information added to the pivot table. So I want it like this:
VDL
2020
14
Wegverkeer
2020
14
Dollars
2020
11
12
13
14
etc

 

[sandy666]

you mean like this (as labels) ?

 

[sandy666]

or like this

 

[Ramballah]

or like this

View attachment 8848

yes but instead of (blank) it should be "Dollars". But I do not care if its $50.000 or $100.000. So just put the week totals in there. Like the € amounts so for the column it should only be 'grand total'