pivot table (xp professional)

Topic

New Reply

Hi, Is it possible to create pivot table with multiple sheets? i have a huge data with as much as 2000000 rows and want to summarize it with help of pivt table. pls help me out.

Reply | Quote

Replies

It can be done (selct “Multiple consolidation ranges”) but it is more limited. The help has additional details

Steve

Reply | Quote

If you can move the data to a database then you can build a pivot table off an external data source. Alternatively you can use ADO with a union query to create a recordset and assign that to your pivotcache.

Reply | Quote

I am interested with this, I hope someone could elaborate with example, please..

Reply | Quote

Try here and here for example.

Reply | Quote

Hi Rory, thanks for reply. I hv tried severel things, but nothing has worked. Can you do it with example in attached sheets? I have to consolidate 6 sheets.

Reply | Quote

You wrote that you have as much as 2 million rows. In Excel 2002 (XP) you’d need more than 30 sheets to store the data, not 6. That doesn’t seem very practical. You should move the data into a single table in a database such as Microsoft Access or SQL Server.

(Just as an illustration, I have attached your workbook with a pivot table based on multiple ranges. See Excel — Pivot Tables — Multiple Consolidation Ranges for a step-by-step description.)

Reply | Quote

Thanks Hans, actually it is excel 2003 and limitation of rows are 65k. I have already done all these exercises. What I need is to get data as per “sample sheet” in attached workbook.

Reply | Quote

Your sample sheet looks exactly the same as the raw data sheets! If that’s what you want, then you will run out of rows for the pivot table just as you would for the raw data.

Reply | Quote

Hi Rory, I am not getting what you suggest. Actually names contains in colums A are common in all sheets. But I am not able to extract information as required. Pl help.

Reply | Quote

My point was that looking at your sample seems to imply that you want to end up with one long list from all the raw data sheets – i.e. you want one line in the pivot table for each line in the raw data. If so, you will run out of rows. If not, can you clarify what the groupings are and whether you just want a straight sum of each of the values.

Reply | Quote

Actually, the data consisting sale of a particular product to different customers in a particular period and requirement is to get detail of one or all customer for whole period in given format. I am doing this with macros,which is very complexd and time taking also. Moreover, it is required to record macro each time a new customer added. The only problem is volume of data. Is there any simple way to do it?

Reply | Quote

It sounds like you want to use a system that is able to handle the larger datasets directly. This suggests to me something more like Access (even if you had XL2007s larger worksheet size)….

If you must keep it in excel in multiple sheets and want to extract some summary sheet, you will have to be specific about what you want the macro to do if you are asking for help in macro writing.

But has been pointed out, if you want to display all the customers in one sheet with details, you will come upon the sheet limitations in XL pre-2007

Steve

Reply | Quote

Hi Steve, This is what I need exactly. can u write some simple macro to perform task?

Reply | Quote

Hi Steve, This is what I need exactly. can u write some simple macro to perform task?

I am not sure whether I should re-open the topic but since it remain un-resolved(so far i am concerned), I take this liberty to do this. It took me almost a year or so (ofcourse I set the issue aside in between) to discover how to consolidate the multiple sheets in a single pivot table. Quote from the link provided by rory:

“It can be done entirely within Excel. Go via menu data, import external data, new database query. Excel files. OK. Select your file. If you don’t have defined names for the data the first time using the software you get a message about no named ranges found, OK past this and when you see options hit “system tables”. This corresponds to worksheets in Excel. Select one worksheet (fields you want) and proceed until you get the option to go to MS Query. Then in MS Query hit the SQL button and amend the text from something like
Code:
SELECT field names
FROM source
to
Code:
SELECT field names
FROM source
UNION ALL
SELECT field names
FROM source_2
and continue to add for each additional table “UNION ALL SELECT field names FROM source_n”

After finishing the SQL hit the open door button (or use the file menu) to end MS Query and then take the pivot table option at the next dialog box.”

I have tested it with as much as 10 sheets having 30K Rows X 8Col each and its amazing.

Yes, it can be done

P.S. : This was my first post and reason alone to join the lounge.

Reply | Quote

I am not sure whether I should re-open the topic but since it remain un-resolved(so far i am concerned), I take this liberty to do this. It took me almost a year or so (ofcourse I set the issue aside in between) to discover how to consolidate the multiple sheets in a single pivot table. Quote from the link provided by rory:

“It can be done entirely within Excel. Go via menu data, import external data, new database query. Excel files. OK. Select your file. If you don’t have defined names for the data the first time using the software you get a message about no named ranges found, OK past this and when you see options hit “system tables”. This corresponds to worksheets in Excel. Select one worksheet (fields you want) and proceed until you get the option to go to MS Query. Then in MS Query hit the SQL button and amend the text from something like
Code:
SELECT field names
FROM source
to
Code:
SELECT field names
FROM source
UNION ALL
SELECT field names
FROM source_2
and continue to add for each additional table “UNION ALL SELECT field names FROM source_n”

After finishing the SQL hit the open door button (or use the file menu) to end MS Query and then take the pivot table option at the next dialog box.”

I have tested it with as much as 10 sheets having 30K Rows X 8Col each and its amazing.

Yes, it can be done

P.S. : This was my first post and reason alone to join the lounge.

Stuck…..

When i am adding new data source in edit query, displays a message ” file name is not valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long””.

Any idea????

Reply | Quote

Stuck…..

When i am adding new data source in edit query, displays a message ” file name is not valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long””.

Any idea????

Maybe I am stating the obvious, but it seems to me the message is indicating that the filename you are entering is invalid. Have you made sure that the name does not include invalid characters or punctuation and that it is not too long?

Reply | Quote

Maybe I am stating the obvious, but it seems to me the message is indicating that the filename you are entering is invalid. Have you made sure that the name does not include invalid characters or punctuation and that it is not too long?

Sure Steve, Name of first file is “Inv 300608” for which query is already set with desired results. I am trying to incorporate another file named “Inv 300908”. Earlier I have made a pivot table with 4 different files named “Inv 300607″,”Inv 300907″,”Inv 311207”, “Inv 310308”, sucessfully . I dont think there is anything wrong with name.

Reply | Quote

Sure Steve, Name of first file is “Inv 300608” for which query is already set with desired results. I am trying to incorporate another file named “Inv 300908”. Earlier I have made a pivot table with 4 different files named “Inv 300607″,”Inv 300907″,”Inv 311207”, “Inv 310308”, sucessfully . I dont think there is anything wrong with name.

But while you do not, it seems that Excel does. If you can do it with other names and only this causes the problem, it still seems to me that something is wrong with that name or the file with that name…

Steve

Reply | Quote

But while you do not, it seems that Excel does. If you can do it with other names and only this causes the problem, it still seems to me that something is wrong with that name or the file with that name…

Steve

I have already done the exercise to do it with different names without any success. I have changed the name of wb as weel as ws, but no results. What next???

Reply | Quote

I have already done the exercise to do it with different names without any success. I have changed the name of wb as weel as ws, but no results. What next???

here is the screeshot of problem. What to do next????

Reply | Quote

Try removing the “$” in the name…

Steve

Reply | Quote

Try removing the “$” in the name…

Steve

No use of removing as excel put “$” in name by default and removing this cause invalid file name. I am not able to recognize the problem as it is doing well with other files with similar names /formats.

It is still worthwhile to use the function to consolidate multiple sheets to create pivot table irrespective of the fact that I am facing a problem like this.

Reply | Quote

Is the worksheet inv_300908 in the same workbook as inv_300608 or in a different workbook?

Reply | Quote

Is the worksheet inv_300908 in the same workbook as inv_300608 or in a different workbook?

No, both sheets are in different WB. Combining then into one is causing problem in saving wb.

Reply | Quote

The SQL statement in your screenshot will only work if the sheets are in the same workbook. If the sheets are in different workbooks, you have to provide the path and filename of each workbook:

SELECT …
FROM `C:ExcelBook1.xls`.`inv_3000608$` `inv_300608$`
UNION ALL
SELECT …
FROM `C:ExcelBook2.xls`.`inv_3000908$` `inv_300908$`

Reply | Quote

The SQL statement in your screenshot will only work if the sheets are in the same workbook. If the sheets are in different workbooks, you have to provide the path and filename of each workbook:

SELECT …
FROM `C:ExcelBook1.xls`.`inv_3000608$` `inv_300608$`
UNION ALL
SELECT …
FROM `C:ExcelBook2.xls`.`inv_3000908$` `inv_300908$`

is there a way to make excel understand to select entire path by default, as it select the file name only regardless to open/close wb?

Reply | Quote

I’m sorry, I don’t understand your question.

Reply | Quote

I’m sorry, I don’t understand your question.

How can I post screen shot with reply?

Reply | Quote

You have already posted several attachments, so what is the problem?

You can use the PrintScreen key to place a copy of the screen on the clipboard.
Paste into Paint, then use the Paint tools to create an image that contains only the relevant part.
Save as a .jpg or .png file.

Or attach a workbook with the relevant information.

See Adding Or Removing Attachments To/From A Post in our FAQ.

Reply | Quote

While setting query, it looks like this :

File used as database in closed and browse the same through dialogue box.

On editing the query, instead of displaying entire path, it displayes file name only.

I just want to know whether i should insert the path manually on editing or excel can pick it automatically?

Reply | Quote

If you want to combine several workbooks in one query, you’ll have to enter the path yourself.

Reply | Quote

If you want to combine several workbooks in one query, you’ll have to enter the path yourself.

Wht is the problem with syntex??

Reply | Quote

You’ll have to use something like

Code:

SELECT `inv_300608$`.`Inv-No`, `inv_300608$`.`Inv-Date`, ...
FROM `E:PrasadSales TaxNMPLReturnAnnual Return8-09Inv-wise SalesQ1`.`inv_300608$` `inv_300608$`

Note:
– The consistent use of `
– The path and filename are specified only once.
– The extension .xls is omitted.

Reply | Quote

You’ll have to use something like

Code:

SELECT `inv_300608$`.`Inv-No`, `inv_300608$`.`Inv-Date`, ...
FROM `E:PrasadSales TaxNMPLReturnAnnual Return8-09Inv-wise SalesQ1`.`inv_300608$` `inv_300608$`

Note:
– The consistent use of `
– The path and filename are specified only once.
– The extension .xls is omitted.

Thanks Hans, very well doing with desired results.

Reply | Quote