Excel 2016 Pivot table from multiple tables

Question

Scenario: I download a monthly statement each month The statement has transaction dates. I give each line of the statement a category. I want to analyse the statement by month using a PivotTable. Each month when I download a new statement, I want to add this into my PivotTable that consolidates all statements that I download. N.B. as you may know, statements don't always run from beginning to end of a month as the statement date may be mid-month therefore there is sometimes an overlap (see example below).

I don't want to copy and paste each month's statement into a consolidated table as I'm sure that PivotTables can handle data in more than one table.

When I build my PivotTable (see below), I want to summarise the date as I've currently got it set up.

My problem is, I don't want to see the values in separate columns (which are the different statements) within the Transaction Period. I don't care what statement they come from. I just want to analyse by Transaction Period.

*I have asterisked the value that come from two separate statements that I want to just appear in one column.

Answer 1

Ciao,

I'm Ugo, a Microsoft Independent Advisor willing to help other Microsoft customers.

Regarding your question, please look at the below:

https://support.office.com/en-us/article/consol...

Please let me know if this helped somehow.

Ciao

Ugo

Answer 2

Note: you cannot simply paste images into the input box (apparently MS programmers don't know now to allow that)

Yes, PivotTables can handle more than one input, Generally (in my limited experience) they import the data from the separate sources to the table they are working from. 

If you want to get fancy, you can use "get and transform" to import the data from the new statements.  

You don't say what format the statements are in. 

Here are some articles on Get and Transform in general

@ Get & Transform in Excel 2016 Intro -   https://support.office.com/en-us/article/Get-Transform-in-Excel-2016-881c63c6-37c5-4ca2-b616-59e18d75b4de 
Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs. These features are also used in Power BI, and in the Power Query Add-In available for previous versions of Excel. Links to more info about using the feature. 

@ Getting Started with Get & Transform in Excel 2016 https://support.office.com/en-us/article/Getting-Started-with-Get-Transform-in-Excel-2016-a8310388-2a12-438c-9d29-c6d29cb8df6a 
With Get & Transform in Excel 2016, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports. 

**********************

Here are some links on consolidating data from multiple sources
Combine data from multiple data sources (Power Query) https://support.office.com/en-us/article/Combine-data-from-multiple-data-sources-Power-Query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d 
In this tutorial, you'll use Power Query's Query Editor to import data from a local Excel file that contains product information, and from an OData feed that contains product order information. You perform transformation and aggregation steps, and combine data from both sources to produce a Total Sales per Product and Year report.

Combine Data From Two Excel Files in Pivot Table September 1, 2010
On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot...

Combine Data on Two Worksheets January 22, 2015
|Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with PowerPivot, and was impressed by what could be done with...

Consolidate- Create a Pivot Table from Multiple Worksheets https://excelchamps.com/blog/create-pivot-from-multiple-worksheets/ 
Assume that you want to analyze the sales data of your company and you pull out the yearly data for the last 4 years. All the Sheets contain the same type of data, but the number of rows can be different. To analyze this data, you need to make a single pivot table report from these multiple sheets – How would you do that?  

old school: Consolidate- Macro Creates Excel Pivot Table From Multiple Files August 30, 2010  http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/ 
If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality. Last...

Get & Transform: An Alternative to Copy Paste Append 
https://www.excel-university.com/get-transform-an-alternative-to-copy-paste-append/ 
September 1, 2016 Jeff Lenning 	
 
Let’s say you have several data tables, and you need to combine them into a single table. One option would be to copy and paste to append them. But, depending on how many data tables there are, this type of manual process can be tedious. In this post, we’ll use a Get & Transform query as an alternative to the copy/paste append method. 

**************************************************************
If the input data is a semi table form, you may have to 'unpivot" it before you can import it

Unpivot- Get & Transform- An Alternative to Manually Flattening Data   http://www.excel-university.com/get-transform-an-alternative-to-manually-flattening-data/ 
June 29, 2016 Jeff Lenning
 
Since many of Excel’s features are designed to work with data stored in a flat, tabular format, we sometimes need to flatten data that is received in other formats. In a previous post, we discussed a manual way to flatten data. In this post, we’ll use a Get & Transform Query as an alternative to […] Read more

*************************************************************

Here are some videos on pivot tables and get and transform

#3 10 pivot table problems and easy fixes – 11 min https://exceljet.net/plc/10-pivot-table-problems-and-easy-fixes 
  1.	New data not included when you refresh
  2.	You want totals and percent of total
  3.	You don't want a data sheet
  4.	You don't like the automatic field names
  5.	Your number formatting gets lost
  6.	You want to see the data behind a subtotal
  7.	Grouping one pivot table affects another
  8.	Refreshing a pivot messes up column widths
  9.	Field headings make no sense and add clutter
  10.	There are blanks in your pivot table

MyExcelOnline 2 Pivot table webinars by John Michaloudis and Oz Du Soleil
https://www.myexcelonline.com/free-excel-webinars  

Free Power Query & Data Cleansing Webinar   118min  ET GT- myexcelonline PowerQuery & Data Cleaning Webinar.mp4 
  *  The most powerful feature in Excel since VLOOKUP...it's called POWER QUERY (GET & TRANSFORM) & it will save you HOURS each day!!! 
  *  Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros! 
  *  Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development! 

myonlinetraininghub.com-  Mynda Treacy
http://www.myonlinetraininghub.com/excel-webinars 

webinar 1-  Creating Excel Dashboards Webinar 62min -  
How to build an interactive Excel Dashboard that you can update in less than one minute

Webinar 2- Dashboards With Power Query & PowerPivot  53min   
how to use Power Query to get data from multiple sources, filter and clean it, send it to PowerPivot and create relationships between data tables.
Then I'll mash it up in PivotTables to create this interactive Excel Dashboard.

*******************************************************

Sounds like you will want to remove duplicate rows

Extract a Unique List – 6 Ways to Remove Duplicates https://www.myonlinetraininghub.com/excel-extract-a-unique-list 
August 22, 2017 by Mynda Treacy 
As with most things in Excel, there are many ways to extract a list of unique or distinct values from a column of data. We’ll look at the options, and the pros and cons of each.
  - Formula 
  - Data tab > Remove Duplicates
  - Filter
  - PivotTable 
  - PowerQuery
  - Conditional formatting of Cells or Rows 	

Dedupe- Remove duplicates & sort a list using Pivot Tables http://chandoo.org/wp/2010/09/27/remove-duplicates-using-pivot-tables/ 
Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it.
It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8).
But today I want to show you a technique I have been using to dynamically extract and sort all unique items from a last list of values using Pivot Tables & OFFSET formula.

3 Ways to Delete Entire Blank Rows + Video Tutorial https://www.excelcampus.com/tips/delete-entire-blank-rows/ 
Jon Acampora	October 5, 2017
Learn a few ways to delete entire blank rows from a data set or table in this article and video.

Answer 3

Pivot from multiple table is something which is a new capability in Excel and is called Powerpivot. There are a lot of videos on Youtube to learn this.