A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Consolidate Pivot table from multiple files



 
 
Thread Tools Display Modes
  #1  
Old January 5th 08, 02:07 AM posted to microsoft.public.excel.misc
aw
external usenet poster
 
Posts: 19
Default Consolidate Pivot table from multiple files


Would like to know the way I can consolidate 3 similar files (but in
different worksheets – eg. File A for China, File B for UK, ..) into one
pivot table.

I try using pivot table function and I can select only “Row”, “Column” &
“value” in “Construct your pivot table report” menu.

What I have to do is to have pivot table that can analysis all individual
fields.

Eg. Consolidated Pivot table row select : “country” – from different source
files
Consolidated Pivot table column select : “Nature”
And sum of “Sales”, “Pur”, “Bank” … under the pivot table


How can I get this??


One of the source file like :
LOCATION : CHINA INV PO PRODUCT Nature SALE PUR BANK AR
SI0001 PO231 Misc Sales 300 300
SI0001 PO231 Misc Purchases 200 -200
SI0001 PO231 Misc Expenses -100
SI0001 PO231 Misc bank chg -20
SI0001 PO231 Misc payment
SI0001 PO231 Misc Amount Rec'd


SI0001 PO238 blue a Sales 300 300
SI0001 PO238 blue a Purchases 200 -200
SI0001 PO238 blue a Expenses -100
SI0001 PO238 blue a bank chg -20
SI0001 PO238 blue a payment
SI0001 PO238 blue a Amount Rec'd

SI0001 PO238 yellow c Sales 300 300
SI0001 PO238 yellow c Purchases 200 -200
SI0001 PO238 yellow c Expenses -100
SI0001 PO238 yellow c bank chg -20
SI0001 PO238 yellow c payment
SI0001 PO238 yellow c Amount Rec'd
--
aw
Ads
  #2  
Old January 5th 08, 11:03 AM posted to microsoft.public.excel.misc
Roger Govier[_3_]
external usenet poster
 
Posts: 2,480
Default Consolidate Pivot table from multiple files

Hi

Multiple consolidation in Pivot Tables will not provide what you want.
You nee to add an extra field (column) to each of the sheets title Country
Fill this column with the name of the country concerned.
Copy the data from all three sheets to ne new sheet, with just a single
header row.
Use this sheet as the source data for your Pivot Table

--

Regards
Roger Govier

"aw" > wrote in message
...
>
> Would like to know the way I can consolidate 3 similar files (but in
> different worksheets – eg. File A for China, File B for UK, ..) into one
> pivot table.
>
> I try using pivot table function and I can select only “Row”, “Column” &
> “value” in “Construct your pivot table report” menu.
>
> What I have to do is to have pivot table that can analysis all individual
> fields.
>
> Eg. Consolidated Pivot table row select : “country” – from different
> source
> files
> Consolidated Pivot table column select : “Nature”
> And sum of “Sales”, “Pur”, “Bank” … under the pivot table
>
>
> How can I get this??
>
>
> One of the source file like :
> LOCATION : CHINA INV PO PRODUCT Nature SALE PUR BANK AR
> SI0001 PO231 Misc Sales 300 300
> SI0001 PO231 Misc Purchases 200 -200
> SI0001 PO231 Misc Expenses -100
> SI0001 PO231 Misc bank chg -20
> SI0001 PO231 Misc payment
> SI0001 PO231 Misc Amount Rec'd
>
>
> SI0001 PO238 blue a Sales 300 300
> SI0001 PO238 blue a Purchases 200 -200
> SI0001 PO238 blue a Expenses -100
> SI0001 PO238 blue a bank chg -20
> SI0001 PO238 blue a payment
> SI0001 PO238 blue a Amount Rec'd
>
> SI0001 PO238 yellow c Sales 300 300
> SI0001 PO238 yellow c Purchases 200 -200
> SI0001 PO238 yellow c Expenses -100
> SI0001 PO238 yellow c bank chg -20
> SI0001 PO238 yellow c payment
> SI0001 PO238 yellow c Amount Rec'd
> --
> aw


  #3  
Old January 5th 08, 02:47 PM posted to microsoft.public.excel.misc
Herbert Seidenberg
external usenet poster
 
Posts: 1,180
Default Consolidate Pivot table from multiple files

Pivot Table > Multiple Consolidation Ranges >
Select: I will create the page fields
In Step 2b, enter and add the ranges from the 3 sheets.
Click "1" at: How many page fields do you want?
In All Ranges: Click on the first range. It may say
China!A1:K50
Type into Field One: "China"
Click on the second range. It may say
UK!A1:L50
Type into Field One: "UK" or "England"
Repeat for the rest of the ranges in All Ranges.
Layout: Drag Page1 to PAGE
Finish.
In the upper left corner of your PT,
you will have a Page1 field from which you can select
"China", "UK", "All", etc
  #4  
Old January 7th 08, 02:13 AM posted to microsoft.public.excel.misc
aw
external usenet poster
 
Posts: 19
Default Consolidate Pivot table from multiple files

Dear Sir / madam,

The result becomes :

Page1 (All)

Sum of Value Column
Row Perod Date A/P A/R …
abc company 0 157588
bcd company 0 118300
cde company 0 275991
… 0 591585
… 0 236528


I can now consolidate all data. But how can I analysis those COLUMN & ROW
fields under PT like single range’s PT??.

If PT cannot do in this way, any method I can consolidate all source files
into 1 single file automatically.

What I’m now trying is setting range for each file & then create queries &
import into 1 single file (refreshable).
Eg. File – UK, import to this single by using row 1 to 500, China by using
row 501 to 1000 ..etc. and using this single database to create PT for my
analysis (refreshable).

However it doesn’t work always & find some problems of duplicate / missing
records.

Actually what I want to obtain is creating PT for multiple files but have
all selective fields for analysis like the one in PT that using single file.

SQL, VB any way I can do this automatically??

--
aw


"aw" wrote:

>
> Would like to know the way I can consolidate 3 similar files (but in
> different worksheets – eg. File A for China, File B for UK, ..) into one
> pivot table.
>
> I try using pivot table function and I can select only “Row”, “Column” &
> “value” in “Construct your pivot table report” menu.
>
> What I have to do is to have pivot table that can analysis all individual
> fields.
>
> Eg. Consolidated Pivot table row select : “country” – from different source
> files
> Consolidated Pivot table column select : “Nature”
> And sum of “Sales”, “Pur”, “Bank” … under the pivot table
>
>
> How can I get this??
>
>
> One of the source file like :
> LOCATION : CHINA INV PO PRODUCT Nature SALE PUR BANK AR
> SI0001 PO231 Misc Sales 300 300
> SI0001 PO231 Misc Purchases 200 -200
> SI0001 PO231 Misc Expenses -100
> SI0001 PO231 Misc bank chg -20
> SI0001 PO231 Misc payment
> SI0001 PO231 Misc Amount Rec'd
>
>
> SI0001 PO238 blue a Sales 300 300
> SI0001 PO238 blue a Purchases 200 -200
> SI0001 PO238 blue a Expenses -100
> SI0001 PO238 blue a bank chg -20
> SI0001 PO238 blue a payment
> SI0001 PO238 blue a Amount Rec'd
>
> SI0001 PO238 yellow c Sales 300 300
> SI0001 PO238 yellow c Purchases 200 -200
> SI0001 PO238 yellow c Expenses -100
> SI0001 PO238 yellow c bank chg -20
> SI0001 PO238 yellow c payment
> SI0001 PO238 yellow c Amount Rec'd
> --
> aw

  #5  
Old January 7th 08, 08:04 PM posted to microsoft.public.excel.misc
Herbert Seidenberg
external usenet poster
 
Posts: 1,180
Default Consolidate Pivot table from multiple files

If you have multiple row fields or your column field
is listed in rows, then
Multiple Consolidation Ranges will not work.
You can consolidate your data on one sheet
using names that refer to the source data.
Maybe like this:
http://www.freefilehosting.net/download/3a5ea
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Pivot Table to consolidate multiple worksheets Dianna S Excel Discussion (Misc queries) 1 June 7th 07 12:32 PM
How do I consolidate multiple worksheet files into one workbook Bentbrip Excel Discussion (Misc queries) 1 March 15th 07 09:29 PM
How can I consolidate files in Excel? charis Excel Discussion (Misc queries) 1 February 5th 07 07:55 PM
how do I consolidate multiple pivot tables into one pivot table? pkahm Excel Discussion (Misc queries) 0 April 20th 06 09:48 PM
Consolidate data from several worksheets via pivot table mthatt Excel Worksheet Functions 0 March 23rd 05 06:51 PM


All times are GMT +1. The time now is 04:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.