Too much data for Excel 1

[Gavona]

I am have been passed the following problem:

We often have a problem when downloading data from SAP (particularly for pay data) when the data exceeds the maximum capacity for a worksheet. We therefore have to break the data into chunks. I had an example where I had a years worth of data copied into 12 separate workbooks and I then wanted to create a pivot combining the data in all 12 sheets.
Step 1 on the pivot wizard allows you to choose 'Multiple consolidation ranges' but in Step 2 this only allows for up to 4 page fields to be used, is it possible to increase the page field selection?

I think the long term answer is to get the data a little more summarised from the source - we are pursuing that. Indeed we are trying to get a report from SAP BI that avoids us doing much at all.

We are not allowed, by corporate instruction, to use Access databases - not supported.

Any ideas as to what avenues I suggest they explore?


Thanks in advance

Gavin

 

[SkipVought]

Gavin,

Chop it up into sheets.

Use an inner sub query, MS Query to join the data via UNION ALL and then summarize in the outer query.

Select .....and summarize here.........
From
(
Select *
From [Sheet1$]

UNION ALL

Select *
From [Sheet2$]

UNION ALL

......
)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Thanks Skip,

Not at all skilled with query let alone using it with SQL (I assume that is what that is).
I'll pass on the advice for now. See if we can persuade someone else in the office to build their expertise. (Though I can't resist the challenge of learning about something new for long).

Gavin

 

[SkipVought]

Data > Import > New Database Query...

Select the EXCEL driver and drill down to the workbook.

Once the QueryTable is added to the sheet, it can merely be REFRESHED, if any of the source tables (sheets) change.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

I don't suppose you could upgrade to Office 2007, could you?

The number of rows has been increased from 65,536 to 1,048,576 (not that I'd try to base a PT off of a million rows of data....)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

No chance at all! ..... Well actually that is a thought. We would only need a few PCs to have 2007 to do this and like tasks....and we do provide Excel models etc to schools some of who do use 2007. We need a way of checking compatibility........

Problem (that pivots solve quickly and easily) is that the data is normalised. Query would summarise it quite a bit but we want a column per month in the report. Can Excel Query deliver that sort of report (cross-tab is the term I think)?




Gavin

 

[SkipVought]

Yes, that kind of query is refered to as a TRANSFORM query...

Transform [i]aggregation[/i]

Select ...
From...
Where...
Group By...
Having...

Pivot [i]across elements[/i]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...or...

you could start with the PivotTable wizard and use the EXTERNAL data source, query the data, as in MS Query and then report it in your workbook as a PT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

OR do we want to create OLAP Cubes (just found something about them but haven't really followed)

http://etl-tools.info/en/bi/excel_pivot_tables.htm

Thanks for the ideas. They need cogitating. Any good links to examples, walk-throughs etc appreciated.

Gavin