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