create pivot table from multiple sheet (excell 2003)

as understood, excell 2003 have row limit...but, currently i have data that 
more than the row limit...
so, i split my data to 2 separate worksheet...the column name for each 
worksheet same..

my question, how can i create one pivot table from the two worksheet...i've 
tried using the wizard n consolidated data but failed....

thank you vm..
0
waklula (2)
10/30/2008 10:31:02 AM
excel 39879 articles. 2 followers. Follow

9 Replies
798 Views

Similar Articles

[PageSpeed] 38

Maybe you could try creating the pivot directly from the Access source table 
(which doesn't have the 65k row limitations)

In Excel, in a new sheet,
Click Data > Import External data > Import data 
Navigate to where the Access file is > Select & Open > Select Table 

In the Import Data dialog: 
Click on "Create a PivotTable Report", 
and you'd then be in the familiar Step 3 of Pivot wiz: Click Layout ... 
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
--- 
"waklula" wrote:
> as understood, excell 2003 have row limit...but, currently i have data that 
> more than the row limit...
> so, i split my data to 2 separate worksheet...the column name for each 
> worksheet same..
> 
> my question, how can i create one pivot table from the two worksheet...i've 
> tried using the wizard n consolidated data but failed....
> 
> thank you vm..
0
demechanik (4694)
10/30/2008 1:20:02 PM
Hi,

Yes you can, using a union query.  It's not easy and I'm at work so if you 
are interested post some sample data so I can see the Field Names and I will 
look at it when I get home.

The Access idea work also, since Access does not have any row limit, it only 
has a size limit.  

Another option is to upgrade to 2007 which has 1,048,576 rows.
-- 
Thanks,
Shane Devenshire


"Max" wrote:

> Maybe you could try creating the pivot directly from the Access source table 
> (which doesn't have the 65k row limitations)
> 
> In Excel, in a new sheet,
> Click Data > Import External data > Import data 
> Navigate to where the Access file is > Select & Open > Select Table 
> 
> In the Import Data dialog: 
> Click on "Create a PivotTable Report", 
> and you'd then be in the familiar Step 3 of Pivot wiz: Click Layout ... 
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:19,500 Files:362 Subscribers:62
> xdemechanik
> --- 
> "waklula" wrote:
> > as understood, excell 2003 have row limit...but, currently i have data that 
> > more than the row limit...
> > so, i split my data to 2 separate worksheet...the column name for each 
> > worksheet same..
> > 
> > my question, how can i create one pivot table from the two worksheet...i've 
> > tried using the wizard n consolidated data but failed....
> > 
> > thank you vm..
0
10/30/2008 2:32:14 PM
Shane,
> Yes you can, using a union query ..

I'd be keen to learn/use the above approach in xl2003

Suppose I have a simple 3 col table below,
identical structure in 3 sheets: Sheet1/2/3
where the combined rows exceed 65k

      StaffID Prod1 Prod2
      2222 200 120
      1111 170 190
      2222 130 180
      1111 200 150
etc

how would I be able to draw out a "normal" pivot table
on the combined lot? Thanks
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000, Files:362, Subscribers:62
xdemechanik
---  


0
demechanik (4694)
10/31/2008 1:59:17 AM
Hi,

I created an ODBC connection to an Excel file within the Pivot Table wizard 
and then I modified the SQL statement in MS Query.  Here is the SQL

This is a union query to 4 sheets in the same Excel file.  Each range 
contained only 2200 records, but that is not the point.  And as you can see 
there were quite a number of fields.

SELECT Customer, `Company Name`, Address, City, Region, `Postal Code`, 
Country, Salesperson, `Order ID`, `Order Date`, `Required Date`, `Shipped 
Date`, `Company Name1`, Product, `Product Name`, `Unit Price`, Quantity, 
Discount, `Extended Price`, Freight
FROM East
UNION
SELECT Customer, `Company Name`, Address, City, Region, `Postal Code`, 
Country, Salesperson, `Order ID`, `Order Date`, `Required Date`, `Shipped 
Date`, `Company Name1`, Product, `Product Name`, `Unit Price`, Quantity, 
Discount, `Extended Price`, Freight
FROM West
UNION
SELECT Customer, `Company Name`, Address, City, Region, `Postal Code`, 
Country, Salesperson, `Order ID`, `Order Date`, `Required Date`, `Shipped 
Date`, `Company Name1`, Product, `Product Name`, `Unit Price`, Quantity, 
Discount, `Extended Price`, Freight
FROM North
UNION
SELECT Customer, `Company Name`, Address, City, Region, `Postal Code`, 
Country, Salesperson, `Order ID`, `Order Date`, `Required Date`, `Shipped 
Date`, `Company Name1`, Product, `Product Name`, `Unit Price`, Quantity, 
Discount, `Extended Price`, Freight
FROM South

If this helps, please click yes.
-- 
Thanks,
Shane Devenshire


"Max" wrote:

> Shane,
> > Yes you can, using a union query ..
> 
> I'd be keen to learn/use the above approach in xl2003
> 
> Suppose I have a simple 3 col table below,
> identical structure in 3 sheets: Sheet1/2/3
> where the combined rows exceed 65k
> 
>       StaffID Prod1 Prod2
>       2222 200 120
>       1111 170 190
>       2222 130 180
>       1111 200 150
> etc
> 
> how would I be able to draw out a "normal" pivot table
> on the combined lot? Thanks
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,000, Files:362, Subscribers:62
> xdemechanik
> ---  
> 
> 
> 
0
10/31/2008 2:27:01 AM
Shane, thanks

I'm stuck trying your opening lines (never done this before) ..
> I created an ODBC connection to an Excel file
> within the Pivot Table wizard
> and then I modified the SQL statement in MS Query ...

Could you give some step by steps?
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000, Files:362, Subscribers:62
xdemechanik
---  


0
demechanik (4694)
10/31/2008 6:15:11 AM
Hi,

The normal way to do this is to connect to an Acces database as shown in my 
example below:

Choose Data, PivotTable & PivotChart Report.  On the first screen choose 
External Data Source, Next, click Get Data, choose MS Access Database and 
click OK.  Navigate to your Access file location and select it.  You will get 
a screen where you can pick the table/query (s) that you want to use.  And 
then you can expand any of them and move the fields to the right.  Then click 
Next three times since you don't need to do anything on the intermediate 
steps for this example.  On this step choose to View data with MS Query click 
Finish.  In Microsoft Query click the SQL button.  You can modify the SQL 
statement, but you really need to know what you are doing.  

For Excel:

First, name the ranges in your sourse file where your data is located, 
include the titles in the range.  Save and close the data source file.

When you want to connect to an Excel file(s) you go through the same 
inititlal steps as outlined for Access above, and when you are in the Choose 
Data Source dialog box you pick Excel Files, Ok and find and select your 
file.  You will be in the first stage of the wizard as discussed above.  
Proceed as above with one of the data ranges only.  You must create the UNION 
yourself that's why I gave you the sample SQL.


If this helps, please click the Yes button.
-- 
Thanks,
Shane Devenshire


"Max" wrote:

> Shane, thanks
> 
> I'm stuck trying your opening lines (never done this before) ..
> > I created an ODBC connection to an Excel file
> > within the Pivot Table wizard
> > and then I modified the SQL statement in MS Query ...
> 
> Could you give some step by steps?
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,000, Files:362, Subscribers:62
> xdemechanik
> ---  
> 
> 
> 
0
10/31/2008 7:32:00 PM
Thanks for the steps, Shane.

Tested it with some data from 2 sheets, the pivoting on the combined data 
seems ok, as long as its < 65k. I hit problems if the combined data exceeded 
65k.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---  


0
demechanik (4694)
11/1/2008 4:11:44 AM
TQVM SHANE & MAX...
to max, 
i managed to create a pivot table using the step given by max (import data 
directly from Access) ....tq2...:)

to shane, 
ur suggestions is much appreciated...but i am new to excell..therefore, ur 
step seems like complicated to me...but tqvm again for ur support.. 

"Max" wrote:

> Thanks for the steps, Shane.
> 
> Tested it with some data from 2 sheets, the pivoting on the combined data 
> seems ok, as long as its < 65k. I hit problems if the combined data exceeded 
> 65k.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:20,000 Files:362 Subscribers:62
> xdemechanik
> ---  
> 
> 
> 
0
waklula (2)
11/3/2008 6:16:00 AM
It's a pleasant surprise to hear from you. Thanks for feeding back. Glad the 
"from Access" option worked out ok for you.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
--- 
"waklula" <waklula@discussions.microsoft.com> wrote in message 
news:B0CC4A4D-ED4D-489B-87B5-5DE8E76AD1ED@microsoft.com...
> TQVM SHANE & MAX...
> to max,
> i managed to create a pivot table using the step given by max (import data
> directly from Access) ....tq2...:)
>
> to shane,
> ur suggestions is much appreciated...but i am new to excell..therefore, ur
> step seems like complicated to me...but tqvm again for ur support..


0
demechanik (4694)
11/3/2008 3:23:33 PM
Reply:

Similar Artilces:

How to stop "Undo" across multiple spreadsheets
When I am working between multiple separate worksheets, Undo will work backwards across all of the worsheets that I have open. How do I get the Undo function to work only on the worksheet that I am currently on? ...

create a roster
How can you create a weekly roster in which functions can be assigned to more than 10 people. The roster should be updatable, so that when holidays and days off are marked on the weekly roster they will be taken account of it future rosters. ...

Outlook 2003 Calendar *from* office *to* home
Hi, I have MS Outlook 2003 on a Win XP pro OS, synchronized to a corporate calendar via Oracle Connector, and I would like to do this (apparently) simple thing: Syncronize events FROM corporate calendar to my local calendar DO NOT Syncronize events FROM my local calendar to corporate calendar. In Outlook I just see a send/receive option, not just send and just receive separate option. Any hint is welcome Stefano ...

A self Instruction Book On Excel 2003
I am trying to find a text book on excel 2003 that will show me how to use Excel and give me the data to put into the spread sheet. Some of the features I am looking for are, Amortization, Creating Macros and The function and Chart Wizard. If you could please include an ISBN# to locate the text to get me started on Excel 2003. John List of books at Debra Dalgleish's site. Some have CD's attached http://www.contextures.on.ca/xlbooks.html Gord Dibben MS Excel MVP On Tue, 2 Oct 2007 12:57:23 -0700, John <John@discussions.microsoft.com> wrote: >I am trying to find ...

importing or creating reference footnotes
I am trying to import a word document into publisher and it has reference footnotes within the document. The reference footnotes are not coming into the publisher document with the reference footnotes. Correct, they will not up to Publisher 2003. In Publisher 2003 depending on which version of Word you are using if they will or not. -- "If you don't know where you are going, any road will take you there!" ...

Mailbox not created when AD account created
A couple of days ago I noticed that I created a user account but the 'Email Addresses' tab of the user properies did not list any email addresses. Subsequent new user accounts have all experienced the same. I researched the knowledge and tried the recommended solutions to either manaully start the recipient update service or rebuild te recipient update service. Neither recommendation has resulted in a mailbox being created for this particular user or any additional user that I have created since this error first showed up. I performed a Windows update last Thursday and so I bel...

Exchange 2000/2003 Public Folder item Permissions Problem
Hi all, I've been having an issue with Public Folder permissions for some time now. The folder contains contacts for all company employees (most of which aren't on the company network), and is maintained by HR. Brief history is: Had Exchange 2000 on Win Server 2000, and a previous administrator modified public folder permissions on the M drive in explorer (a no-no). This resulted in the ESM "Invalid window handle" error. Managed fix these by following KB 270905 (permissions nuke), and deleting and re-creating the public folder. The latter was a problem, as most users were ...

footer in excel #2
What is the easiest way to make a footer in excel that will print ON EVERY PAGE? EXCEL 2000 Page Setup Header/Footer Put your entry in Footer "LED1" <LED1@discussions.microsoft.com> wrote in message news:D226F524-7267-48D9-98AE-A1C768B6921E@microsoft.com... : What is the easiest way to make a footer in excel that will print ON EVERY : PAGE? EXCEL 2000 ...

Task Create - Status
I'm trying to make a simple task, when a user owner is assigned a task in CRM, it receives an email letting it know it has one. I have the following below, but it is not generating one when i assign one manually in the CRM. What to do? I've tried both the following: When task is created E-Mail to:[owner];Subject test As well as: When task is created if Task activity status = Open then E-Mail to:[owner];Subject test I've checked the mailserver many times over, and I can send emails manually from the CRM so you can rule that out Tested out ok on our end. Some ideas:...

Bug with cell merging in tables using Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Once you have merged cells in a table in Word 2008 using Applescript, any further reference to the table's cells in the script produce an error. <br><br>As an example, open Word 2008 and run this script: <br> tell application &quot;Microsoft Word&quot; <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;make new table at active document with properties {number of rows:4, number of columns:4} <br> &nbsp;&nbsp;&nbsp;&nbsp...

Help Creating XmlNode / XmlAttribute Elegantly
I am creating a configuration class to read / write a standard configuration file. <?xml version="1.0" encoding="utf-8"?> <configuration> <appSettings> <add key="ConnectionString" value="server=(local);" /> </appSettings> </configuration> I am using a XmlDocument for my base implementation. I am looking to see if there is a better way to add a new node / attribute than using XmlDocument.InnerXml. I would consider this to be a hack but couldn't really see another way of doing this without definin...

ALT-TAB not working with excel 2000
My problem is, as in the subject, that pressing ALT-TAB to switch among the files opened in Excel 2000 doesn't work anymore. I tried to re-install withouth success. I can only use CTRL-TAB because you haven't multiple sessions running (as usual in Excel 2000) but only one task of Excel. So, it's impossible to use ALT-TAB. Unfortunately, CTRL-TAB isn't useful as ALT-TAB because you can't switch from the current worksheet and the last used but you have to pass cyclicly through the files opened... It seems to be related to the fact that the same worksheets had been opened...

Splitting one table into multiple tables/tabs
Hi I was wondering if anyone can help me split a table into multiple tables or tabs. If I have a table of 20 customers with various transactions over time all in one table. I'd like to know if it's possible to seperate the table into multiple tables based on the change in customer number and have them broken into multiple tabs without manually creating each tab and then cut and pasting. I really appreciate any help! Thanks! ...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

Excel Open and Close
I have a problem, whereby sometimes after closing Excel and then looking on task manager the excel process is still running. When opening excel again all that appears is the menu icons and the grey surrounding the actual spreadsheet. No actual spreadsheet appears. Needless to say it is also locked up. If I close that spreadsheet, and also end the process for one that is running in task manager, upon opening excel again it is fine. I've tried doing detect and repair, but this doesn't help. It is also intermittent as I guess only 50% of the time closing excel will still leave the process...

Query on Group field in Pivot Table
I have a large database wherein under Date column, data is in the format "Thu Oct 1 12:00 AM". When I use Pivot Table Under OPTIONS->GROUP-> Group Field is disabled. I used this to group Date field to either present data by Month, quarter at one click. Can anybody guide me why this seems to be disabled here. I want to present my data only by Monthwithout changing the format ""Thu Oct 1 12:00 AM". Thanks Hi Shewta Check you Data Source of your Pivot. it should come up like "Sheet6!$G$1:$I$821" and not like "'\Documents and...

Pivot Table update without opening the Excel file.
Hi, I have a spreadsheet saved as an xlt. It has three pivot tables which connect to CSV data files using Microsoft Text Driver. The CSV files are updated overnight by an external application. The Excel file is in \\Server\Viewers The Data files are in \\Server\Data Local users use a shortcut to open the Excel file on \\Server\Viewers which the does an auto-update from the CSV files. The pivot table data is updated. All that has been working perfectly for years, internally. Now I need to send the same excel file to another company by email. I have succeeded in writing a vbscript that creates...

tabls in GP90 database
Does anyone know or have a description or documenation on the tables in the database for GP90? Maybe all I need is the important ones. thanks Within the program itself, Tools > Resource Descriptions Also, there are ERD's on the distribution CD's that you got when you purchased or upgraded the system - you have to install the SDK to get at them. -- Lyle U Adam Clark wrote: > Does anyone know or have a description or documenation on the tables > in the database for GP90? Maybe all I need is the important ones. > > thanks ...

Create external database
Hi all. I need to create an external database (file). On a site I found this code: ------------------------------------------------------ Function CreateLinkedExternalTable(strTargetDB As String, strProviderString As String, strSourceTbl As String, strLinkTblName As String) As String 'strTargetDB = Source Database Name 'strProviderString = Not used, currently hard coded 'strSourceTbl = Source Table name in the database we are linking too. 'strLinkTblName = Table name we would like to see in the Access Database. Dim catDB As ADOX.Catalog Dim tblLink As...

After upgrade to Exchange 2003, no more M: drive?
I just want to make sure. After I upgraded to Exchange 2003, I see there is no longer a M:\. Is that normal? Yes, that's normal. Should you want to re-enable it: http://hellomate.typepad.com/exchange/2003/09/no_m_drive_in_e.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "amyb" <amy@npsg.ncifcrf.gov> wrote in message news:#wz#bKlxDHA.1576@TK2MSFTNGP11.phx.gbl... > I just want to make sure. After I upgraded to Exchange 2003, I see there is > no longer a M:\. Is that normal? > > Oh, ok. Thanks....

Data Tables
Has anyone ever had a problem where a data table is producing incorrect values? I have a (two dimensional) table which is producing unexpected values. When I try to replicate these values through manually changing the inputs (and changing nothing else) I get a different result to that produced by the data tables. Just to check I'm not going completely mad, I've had a colleague confirm the symptoms. Any suggestions gratefully received before I head into investigating for corruptions etc Mike The only time I've ever had a problem with charting is when I had merged c...

Download Transaction Problem Money 2003 CDN
When I'm downloadig transaction from my banks webpage the date shown for the bank entry in MS Money is always one day behind. e.g. real transaction date is 16 but in money it's shown as 15. My banks says on their site everything is ok and that it's a setting in money. Also they never heard of this problem before. Any help? ...

How to create pie chart with %
I have got a problem that I don't if excel could help me to calculate the % within a column and draw a pie chart. My case is I have a column containing a series of numbers. e.g. 1,4,8,1,3,9,11,2,4,5,6,.........etc I want to draw a pie chart that showing 1-3 , 4-6, 7-9, >9 with the corresponding % inside that column. Can anyone tell me how can I make it ? Ray - You need to calculate the frequencies in your data. Let's assume your data is in A1:A12. In B1:B3 enter these numbers, the upper limits on the groupings: 3 6 9 You can enter ">9" into B4 witho...

how do I copy a range to a new sheet
Now that I this group solved my problem of flagging dates of my club members as "expired", how would I automatically copy all of the members that are expired to a new worksheet so I can use this for a mail merge? I would need to check the condition of the expired column, YES or NO, then copy the yes's name and address columns only to a new page. TIA Why not just add that column and when you use mailmerge, just include the records with that flag set the way you need? If you really want to copy the rows, you could apply Data|Filter|Autofilter and filter to show just those mem...

Email address not created when a new user is created
All, This issue recently happened in my company. Upon creating a new user, the policy was set to create the smtp and X400 address automaticaly. Now when we create a user the smtp and x400 addresses are not created, and when attempting to setup the new user in outlook, the address cant be found. We are using exchange 2003 SP1 on Win2k3 servers. On Wed, 13 Jul 2005 06:49:07 -0700, "Bryan" <Bryan@discussions.microsoft.com> wrote: >All, >This issue recently happened in my company. Upon creating a new user, the >policy was set to create the smtp and X400 addres...