create pivot table from multiple sheet (excell 2003)

[waklula]

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..

 

[Max]

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

 

[ShaneDevenshire]

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.

 

[Max]

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
---

 

[ShaneDevenshire]

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.

 

[Max]

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
---

 

[ShaneDevenshire]

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.

 

[Max]

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
---

 

[waklula]

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]

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