Pivot Realtionship!!

[meus]

Hello Friends!!!
I have two different data sources from where i extract two different pivot tables and then merge those two manually. I do it every week and tired of doing it manually.... What i want is that i want to make a relationship on these two Pivot tables or may be the two "DATA SOURCES" and display the result in single Pivot table....

The first source contains name, age, DOB and the second source contains the same name, and other headings...
Can this be done??
Thanks for you time...

[NeedForExcel]

Merging Pivot Tables is not possible, you need to merge data sources..

You can do that either by using PowerPivot/Data Model (Available to only certain versions of Excel) to build relationships between multiple data sources or use Vlookup if you have a common key in both data sources, which I suppose you have; "Name" and then build a Pivot Table from that data.

You can post a sample of your data aswell for further clarification..

Hope it helps!

[meus]

Yes i would like to do it by Power pivot or may be by merging data sources... I have common key in both of the data sources... how can we do that?

[:) Sixthsense :)]

Since both source data having the similar structure you can use Multiple Consolidation ranges options while creating Pivot Table.

[NeedForExcel]

Since both source data having the similar structure you can use Multiple Consolidation ranges options while creating Pivot Table.

WOW! This feature is almost forgotten.. Like I did right now.. Its a great feature..

@Meus - You might need to take a look at this to access it..

https://support.office.com/en-NZ/art...0-26f4d9f21ad6 - If using Mac

https://support.office.com/en-nz/art...1-e9fc8adeeeb5 - For Windows

Yes i would like to do it by Power pivot or may be by merging data sources... I have common key in both of the data sources... how can we do that?

You can either do it as SixthSense mentioned above, or by using simple Vlookups.. If you need help with the function kindly post sample data to understand the structure better..

[meus]

Thank you Dosts!!

[meus]

An error occurred...
i tried to relate two tables having one common key "Client" but the following error occured...
aaaaaaaaa.JPG

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[teylyn]

If you use Power Pivot, you can add both tables to the data model and establish a relationship that works similar to a Vlookup.

Or, you can download Power Query, a free add-in from Microsoft, with which you can retrieve and transform (join, merge, format, clean up) data from different data sources into one query. You could use Power Query to remove the duplicates in one of the data sources, then join the two tables and load the result into the data model, from where you can access it with Power Pivot.

cheers, teylyn

[meus]

I have attached the scrap workbook...
In this file, i try to relate the two tables but i am not able to do so...
Thanks

[:) Sixthsense :)]

Please show the expected result

[meus]

Here is it in the attached file the desired result as well...

[meus]

??????????????????????????????

[:) Sixthsense :)]

Actually normal pivot table with the source range of table2 is enough in this case.

You just add a column in Table2 for arriving the Hours from Table1. You can arrive it by using sumif() function.

Refer the attached file for details.

[meus]

No, But that is not the solution... that is just the Scrap Workbook.
In my original workbook, the situation is too complex...
I havve to manually input the data so wanted to relate tables in pivot
Thanks any way

[:) Sixthsense :)]

Since both of your data don't have a constant structure my suggested method won't work for you...

Please check the teylyn suggestion on Post #9 and see whether it solves your expectation.