Pivot Table Source Spread Across Two Separate Sheets

[jocanon]

I have what would otherwise be a single table of data. The table columns are basically: Account Number, Amount, & State. The data in the table shows each sales transaction, what GL account it was recorded to and the state where delivery of the sale took place. The problem is, I have 1,443,812 rows of sales data and since Excel 2013 only goes to 1,048,576 per sheet, I have to break the table up into two separate sheets. So my question is: How can I, or is it even possible to, create a pivot table whose data source includes all the data in both tables as if they were one table? I have Excel version 2013. I tried using the data model but it wants me to create relationships and I am not sure that is the way to go. Also, I tried using the pivot table wizard and created a pivot table by combining the two tables, but the resulting table doesn't have the same options or behave the same way I am use to and I can't figure out how to get a report that I want. In the end, I simply want to show all the sum total sales amounts by account number and by state, with states being in the row header and account number being in the column header.

[mikeTRON]

You can download MySQL and load it to the table, then you a pivot table linked to that table.
You can save it as CSV, or TWO CSV files, then combine them with a .BAT procedure (which is absurdly easy) then import THAT csv into PowerPivot (which is a free add-in) then use PowerPivot instead of Pivot tables.

I had up to 24M records in a CSV using the method I just listed before the company let me have my own SQL table to store the data... o_O
It was something like 700k records a month for about 3 years. The powerpivot handled it just fine but the file size was about 2.4GB lol

I can send a few links that basically walk you through what you need to do, but it is easy and should take less than 5 minutes. Also you will have to remember on anything but the FIRST csv you have to remove the column headers.

[jocanon]

...I can send a few links that basically walk you through what you need to do..

Awesome, thanks. Can you post the links up here? That sounds like exactly what I need.

[jocanon]

You can download MySQL...

I actually have Sequel (well, the company I work for does), I just don't know how to use it. I don't know if this is what you are referring to though.

[mikeTRON]

Yeah SQL can be used to store data as it is a proper database.
Then you can link your pivot table TO that table and avoid having the issue of >1.04M records...


I guess you could also use microsoft access but I always found poewrpivot was a better solution even WITH the silly CSV stuff...

[MarvinP]

Hey Jocanan,

Power Query an Excel Add-In can do what I think you want. You would simply append the two tables and work from that.

Here is one of many links:

http://datapigtechnologies.com/blog/...nto-one-table/

BTW, you will need to install the Add-In but it is from Microsoft and is now included in Excel 2016 as "Get & Transform".

[mikeTRON]

Hey Jocanan,

Power Query an Excel Add-In can do what I think you want. You would simply append the two tables and work from that.

Will PowerQuery allow more than 1.04M records though? I didnt think so... Otherwise PowerQuery is slick and definitely worth learning about.

[jocanon]

Is it easy to load a database to Sequel and link it to a pivot table, I mean, is it something you can tell me how to do or point me to a tutorial that isn't 100 pages long?

Here is the info I copied out of the about Sequel menu option that shows the version:

ViewPoint Version: 10.13.304
System: S1044089.KNIGHTTRANS.COM 07.01.0000 OS/400 V7R1M0
Client Access: 7.1.0 SI47412
Host serial#: 10EC68P
SEQUEL RelMod: R11M03
Job name: 071263/QUSER/QZRCSRVS
User profile: OLSONJ
Client Report: Installed
Administrator: Not Installed

[mikeTRON]

I have never used SEQUEL, only SQL and MyQL so I cannot offer much input on that.
From a high level neither were difficult to use.

[MarvinP]

Here are the Power Query limits.

https://support.office.com/en-us/art...B-6793F051A9F4

[mikeTRON]

"Maximum number of rows filled to worksheet: 1,048,576"

Right, since that is the max number of rows in a worksheet.
Access and PowerPivot can import more as they don't store the data IN a worksheet.


Good suggestion though, I forget about combining data with that add-in!

[jocanon]

Found the answers here. Worked great, thanks mikeTRON:

how to merge csv files:
http://www.excelbee.com/merge-csv-fi...thout-any-tool

Then how to link it with Excel Pivot table:
https://groups.google.com/forum/#!to...ap/zf0U-zSeypQ

Now the resulting pivot table acts just like it would if it was pulling data from a single table. Perfect! Thanks again!

[mikeTRON]

Yeah, and if you wanted to process the data BEFORE it hit the pivot table, that is where you would want PowerPivot. You can create relatonships (like vlookups with a reference table) etc etc.
PowerPivot is great.

http://chandoo.org/wp/2013/01/21/int...o-power-pivot/