View Single Post
  #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