Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2017, 03:09 AM
marelisev marelisev is offline Combine multiple tables into one pivot table in order to do trend analysis Windows 10 Combine multiple tables into one pivot table in order to do trend analysis Office 2013
Novice
Combine multiple tables into one pivot table in order to do trend analysis
 
Join Date: Apr 2017
Posts: 1
marelisev is on a distinguished road
Smile Combine multiple tables into one pivot table in order to do trend analysis

The headline is a bit involved... I have 12 months' worth of expense reports:


12 tables (one table per month) with a list of transactions, ordered by date, category, subcategory & amount.

I want to see the total amounts relating to each payment subcategory.
I already have a pivot table for each table, summarizing the totals per sub-category, but I want another table that summarizes all 12 transaction tables into one pivot table.
I.e. I want one pivot table that shows the total amount spent on each subcategory for each month.

I want this in order to do a line graph analysis to see how totals spent on each subcategory has fluctuated over the year.

If anyone can give tips for either the consolidated pivot table or the line graph afterwards, or both, I would appreciate it a lot!!
Reply With Quote
  #2  
Old 04-14-2017, 05:54 AM
TabH TabH is offline Combine multiple tables into one pivot table in order to do trend analysis Windows 10 Combine multiple tables into one pivot table in order to do trend analysis Office 2016
Novice
 
Join Date: Apr 2017
Posts: 6
TabH is on a distinguished road
Default

Hi

When you insert a pivot table tick the box to add it to the Data Model. In the pivot table field list chose all. This will make all your tables available. You will need to link these tables by a unique (Primary) field. It is like a database with a one to many relationship.

Look under the Analyse tab and you will see relationships. This is where you link the fields.

Eg. if you have a job number list, the job number is a unique number. Many people will work on any of these jobs so the primary field is the job number in the job number list and the linked job number is in the workers list.

I hope this helps.

TabH
Reply With Quote
  #3  
Old 04-15-2017, 08:30 AM
xor xor is offline Combine multiple tables into one pivot table in order to do trend analysis Windows 10 Combine multiple tables into one pivot table in order to do trend analysis Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,078
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Another possibility:

Union Query Macro -- Sheets in One File

Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz

at the below link:

http://www.contextures.com/xlPivot08.html

(About 5/6 down the page)
Reply With Quote
Reply

Tags
line graph, pivot table, trend analysis

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table from multiple sheets hunter1751 Excel Programming 0 04-01-2017 05:08 AM
Combine multiple tables into one pivot table in order to do trend analysis multiple charts in pivot tables olybobo Excel 1 05-06-2016 12:26 AM
Multiple Label Criteria - Pivot Table philipq Excel Programming 4 11-27-2014 09:06 AM
Pivot Table Question- Can the table display in the order the boxes were checked/selected? blackjack Excel 0 09-13-2014 08:57 PM
Break Pivot Into Multiple Tables Pramalot Excel 0 05-22-2012 05:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft