If ou are using Excel 2003, one option you have is to take the data to
Access and then create a pivot in Excel where the data source will be the
Access database.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"heather" wrote in message
...
I have a number of very large datasets of household water consumption
which I
want to combine so I have an average reading per house per year (eg I have
four spreadsheets per year, for an area (or subset of area, it is a city,
and
some are VERY large)). Each row has an identifier, LotPlan, most of which
are
unique, but there are some duplicates (where this is so, they refer to the
same parcel, so I can just average or sum the readings with no problem). I
have deleted all zero records.

What I want to do is run subtotals and average the Avg daily water
consumption per LotPlan (some have four readings, some may only have one).
However, Excel will not let me subtotal, as it gives me the end of data
error, which I figure is related to the size of the spreadsheet. What I
was
doing was appending the data so I had one worksheet with all the
information,
identified by LotPlan, and also by reading period; however, I cannot do
this
either, as then it is more than 65K rows. Are there any ways I can get
around
these problems in Excel 2003 or 2007? If not, can I do it in Access or
SPSS
or other software? I have 4Gig RAM, although it is still very slow! Thanks
for any help.