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

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

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

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

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