I have data of 150,000 record, how to use pivot table ?

[pui]

because max row in excel = 65636 but I have 3 sheet and each sheet contain data for 50,000 row (column A to I).
I try to copy it to 1 sheet and use pivot table to analyse something but couldn't.

anyone have some idea please ?

 

[eliW]

Hi,

I understand that you want to analyse something based on 150,000 records (from 3 worksheets)

Maybe that PT isn't the best option.

Please give some details on your data and what do you want to analyse.

Eli

 

[fairwinds]

Hi,

I don't know if it is possible in your case, but you can use multiple consolidation ranges for a pivot table.

 

[pui]

I dont know how to do multiple selection.
Anway I have data of my warehouse like this data for each product line.
product name, location, volume (m3), class, destination, cost

I want to creat report like :

location class total volume destination total cost
01 A 20 north 30,000 $
B xx south xxx
C xx xxx xxx
02 A xx xx xx
B xx xx xx
03

xx

xx

Anyone can help me please

 

[pui]

how to calculate 150,000 record in pivot table ?

Author Message
pui Posted: Wed Feb 02, 2005 6:31 am Post subject: Re: I have data of 150,000 record, how to use pivot table ?

--------------------------------------------------------------------------------

I dont know how to do multiple selection.
Anway I have data of my warehouse like this data for each product line.
product name, location, volume (m3), class, destination, cost

I want to creat report like :

location class total volume destination total cost
01 A 20 north 30,000 $
B xx south xxx
C xx xxx xxx
02 A xx xx xx
B xx xx xx
03

xx

xx

Anyone can help me please

fairwinds Posted: Tue Feb 01, 2005 9:29 am Post subject: Re: I have data of 150,000 record, how to use pivot table ?

--------------------------------------------------------------------------------

Hi,

I don't know if it is possible in your case, but you can use multiple consolidation ranges for a pivot table.

eliW Posted: Tue Feb 01, 2005 8:42 am Post subject: Re: I have data of 150,000 record, how to use pivot table ?

--------------------------------------------------------------------------------

Hi,

I understand that you want to analyse something based on 150,000 records (from 3 worksheets)

Maybe that PT isn't the best option.

Please give some details on your data and what do you want to analyse.

Eli

pui Posted: Tue Feb 01, 2005 8:22 am Post subject: I have data of 150,000 record, how to use pivot table ?

--------------------------------------------------------------------------------

because max row in excel = 65636 but I have 3 sheet and each sheet contain data for 50,000 row (column A to I).
I try to copy it to 1 sheet and use pivot table to analyse something but couldn't.

anyone have some idea please ?

anyone can help me please .....

 

[daniels012]

When you do a pivot table it asks from where you want to collect the data. In the cell you can type... Sheet1!$A:$A,Sheet2!$A:$A,Sheet3!$A:$A

This gives Column A in Sheet1,2,&3

Hope this helps,
Michael

 

[Mark O'Brien]

I'm not an expert, but aren't PivotTables limited to a maximum of 8,000 items? (and 32,000 if you're using Excel 2003)

 

[RichardS]

Hi Mark,

I've got one made from a trial balance with 8.5k lines.

To the OP, if there isn't a limit, and I'm not saying there isn't, I'd suggest you export your three sheets to Access, and build your PT on external data.

Richard

 

[Mark O'Brien]

Richard, what version of Excel are you using? If it's not Excel 2003, are the 8,500 entries all unique and definitely accounted for?

I'd suggest looking in the Excel help file and searching for "Excel Limitations".

 

[Peter van Vliet]

Hi Pui,

Put the records in a separate file - name ranged prerferably.

Start a new file select:
Data, pivottable
external source
get data select Excel files and name range.
follow through to end of wizard and select Create OLAP cube

Follow OLAP wizard - fairly simple if you play around with it.

NB!!
IN the Pivot wizard your row items must not have more than 65536 uniques.
Drag other fields to filters or columns.

Regards,

Peter