I have data ("Usage") with hours 1-24 as the column headers and dates Jan
1-Dec 31 in rows 1-365 for user 1, rows 366-733 for user 2, etc... for 1200
users. The year 2005 is on the first sheet, 2006 on the second, etc....

I'm going to clean up the data by putting it into an access db with only 4
fields (Date, Hour, User, Usage) (31 million rows) then connect with Excel to
use pivot table functions.

Any good ideas about how to unsummaryize the data?

Hour
User Date 1 2 3 4 5
1 1-Jan 5.00 6.00 4.00 4.00 10.00
1 2-Jan 3.00 2.00 5.00 9.00 3.00
1 3-Jan 11.00 6.00 8.00 2.00 2.00
1 4-Jan 7.00 5.00 8.00 4.00 10.00
1 5-Jan 2.00 8.00 4.00 9.00 7.00

Possible double post - system hiccuped.

First of all, 31 million rows of data is a pretty hefty load for Access.
But for any database I'd use 5 tables :
UsersTable 1200 entries for your users individual identification information;
DatesTable 365 or 366 entries for each year and
HoursTable with up to 24 Hours entries per date (you could possibly do away
with this one and store the Hour as part of the 4th table);

The 4th table, UsageTable, would be a table that holds pointers to the
UsersTable, Dates and to the HoursDates table and containing a usage entry.

To add new usage entries, you'd choose User, Date and Hour from 3 drop downs
and enter the usage on a form. An entry in UsageTable would contain nothing
but Long values for the 3 pointers, plus an integer or single field for the
usage. Fields in it would be your 4 fields. Single record in it might look
like:
USER DATE HOUR Usage
1 2 3 5.00
which is the same as your User 1, 2-Jan-2005, (hour) 3 5.00 entry in the
sample table.
That's about as compact as I can envision it, and relatively easy to create
queries into, and it actually adheres to normalization rules.

Now, what exactly are you looking for from this forum with your question of
"Any good ideas about how to unsummarize the data?" Code to move into Access
tables?, Way of reorganizing it to do the same? Something else?


"Ian" wrote:

I have data ("Usage") with hours 1-24 as the column headers and dates Jan
1-Dec 31 in rows 1-365 for user 1, rows 366-733 for user 2, etc... for 1200
users. The year 2005 is on the first sheet, 2006 on the second, etc....

I'm going to clean up the data by putting it into an access db with only 4
fields (Date, Hour, User, Usage) (31 million rows) then connect with Excel to
use pivot table functions.

Any good ideas about how to unsummaryize the data?

Hour
User Date 1 2 3 4 5
1 1-Jan 5.00 6.00 4.00 4.00 10.00
1 2-Jan 3.00 2.00 5.00 9.00 3.00
1 3-Jan 11.00 6.00 8.00 2.00 2.00
1 4-Jan 7.00 5.00 8.00 4.00 10.00
1 5-Jan 2.00 8.00 4.00 9.00 7.00

Excel 2007 PivotTable
Consolidate year tabs, then
un-summarize or un-pivot or reverse pivot.
With macro, not formulas.
Stay in Excel or go to Access.
http://www.mediafire.com/file/hx1vmwcmjin/12_27_09.xlsm
http://c0444202.cdn.cloudfiles.racks.../12_27_09.xlsm