Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
aw aw is offline
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
  #2   Report Post  
Posted to microsoft.public.excel.misc
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   Report Post  
Posted to microsoft.public.excel.misc
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   Report Post  
Posted to microsoft.public.excel.misc
aw aw is offline
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 ranges PT??.

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

What Im 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 doesnt 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   Report Post  
Posted to microsoft.public.excel.misc
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:29 PM
How can I consolidate files in Excel? charis Excel Discussion (Misc queries) 1 February 5th 07 08: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 07:51 PM


All times are GMT +1. The time now is 02:28 PM.

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

About Us

"It's about Microsoft Excel"