Using powerpivot to consolidate multiple worksheets

[abreen23]

Hi everyone,

I am trying to create one giant pivot table from 3 different tables. I have been playing around with power pivot but can't create any relationships since my data is duplicated or something. I have three tables, each of which have a few columns that are the same- date, account, description and amount. The three tables are comprised of 3 bank statements from Mar-Oct. What I want to do is basically pivot the three tables so that I can list account by date for all of the entries in the 3 tables, however each of my tables may have duplicate dates such as:

Table 1
Date Date Detail CB Account Translation Transaction
2012.04 2012.04.02 IN TRANSIT Funds to be released (211.41)
2012.04 2012.04.02 NET Automatic investment (7,164.27)
2012.04 2012.04.02 12030 Client Payment 211.41
2012.04 2012.04.02 12030 credit card receivable (Cielo) 1,684.00
2012.04 2012.04.02 12030 credit card receivable (Cielo) 1,862.26
2012.04 2012.04.02 N/A available balance 0.00

Table 2
Date Date Detail CB Account Translation Transaction
2012.04 2012.04.02 NOT DETERMINED TO BE CONFIRMED WITH LUCIANA - INVESTMENT IN … (649.63)
2012.04 2012.04.03 12030 RECEIVED FROM SUPPLIER 78.84
2012.04 2012.04.03 NET TRANSFER CHECKING TO CORPORATE CHECKING (100.00)
2012.04 2012.04.03 NET TRANSFER FROM INVESTMENTS 250.00
2012.04 2012.04.04 12030 RECEIVED FROM SUPPLIER 17.68
2012.04 2012.04.05 12030 RECEIVED FROM SUPPLIER 337.90

Is there a way for me to connect these two tables so that I can pivot by 2012.04 and show the sums of transactions by CB Account?

Thank you!

[nsr1989]

Create a new table (say table4) with just the list of dates that you are dealing with(unique dates only)

Create another table (say table5) with just the list of account no. That you are dealing with(unique account no)

Now create relationships like this:-
Link
table1.date to table4.date and table2.date to table4.date
And
Table1.account to table 5.account and table2.account to table5.account



And in pivot table remember to use the date & account from unique table i.e. table 4 and table 5 ...
And put values of table 1,2&3 to the calculated field ..

Hope this will help