Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Pivot Table to consolidate multiple worksheets | Excel Discussion (Misc queries) | |||
How do I consolidate multiple worksheet files into one workbook | Excel Discussion (Misc queries) | |||
How can I consolidate files in Excel? | Excel Discussion (Misc queries) | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
Consolidate data from several worksheets via pivot table | Excel Worksheet Functions |