I have about 1200 data points for temperature, taken every hour each day. The
data is in 3 columns, date, time (0:00-24:00 hrs) and the temp reading for
that hour. I want to put the data into a table with date on the left column,
and the values 0:00 - 24:00 across the top. This will allow me to plot the
data by day, month etc.

I tried a pivot table and couldn't get the data to format the way I needed
it to. Are there other suggestions for automating this task?
Thanks in advance.

Hi

(On fly)
Let you have a sheet Data with a table (headers in row 1): Date, Time, Temp

Add a column (p.e. RepRow) to left your table (column A now, and you can
hide this column later)
Define named range
DataDate=OFFSET(Data!$B$1,1,,COUNT(Data!$B:$B),1)


Add a sheet Periods
On sheet Periods, create a table Day, Month, [Year], with according headings
in row 1.
Into cell Periods!A2 enter the start date.
Periods!A3=IF($A$2+ROW()-2TODAY(),"",$A$2+ROW()-2)
Copy Periods!A3 down as long as is reasonable for you.
Periods!B2=IF(DATE(YEAR($A$2),MONTH($A$2)+ROW()-1,0)TODAY(),"",DATE(YEAR($A$2),MONTH($A$2)+ROW()-2,1))
Format Periods!B2 as "yyyy.mmmm" or "yyyy.mm", and copy also down as long as
reasonable (but ~30 times less rows as column Day)
When you have data for different years in same table too, then you'll need
Year column too
Periods!C2=IF(YEAR($A$2)+ROW()-2YEAR(TODAY()),"",YEAR($A$2)+ROW()-2)
Format as General and copy down for some rows

Define a couple of named ranges
PeriodList=OFFSET(Periods!$A$1,,,1,COUNTA(Periods! $1:$1))
DayList=OFFSET(Periods!$A$1,1,,COUNT(Periods!$A:$A ),1)
MonthList=OFFSET(Periods!$A$1,1,1,COUNT(Periods!$B :$B),1)
[YearList=OFFSET(Periods!$A$1,1,2,COUNT(Periods!$C: $C),1)]


Add a sheet Report.
On sheet Report in row 1:
Into one cell in enter "Period:", for another (it may be next one, but also
further to right - it depends on width of your report columns you'll design
later) apply data validation list with source "Day", "Month" [; "Year"].
Define the cell with data validation as named range Period.

Further to right, for some some cell apply data validation list with source
=IF(Period="Day",DayList,IF(Period="Month",MonthLi st,""))
(When you use period Year too, add an IF-level for it too)
Define this cell as named range Selection

On sheet Data (here the formula when you use "Day" and "Month" as periods
only - when you use "Year" too, the formula will be more complex)
Data!A2=IF(AND($B2<"",IF(Period="Day",$B2=Selecti on,DATE(YEAR($B2),MONTH($B2),1)=Selection),"",SUMP RODUCT(--($B$2:$B2)=Selection),--($B$2:$B2)<=IF(Period="Day",Selection,INDEX(MonthL ist,MATCH(Selection,MonthList,0)+1,)))))
Copy the formula down at least for entire table. When all was right above,
you'll have numbered all rows in Data table, which match with conditions
estimated on Report sheet.

Now you have to create a report table on sheet report - using functions
ROW() to estimate report row number, and VLOOKUP(Data!$A?,...) to get
according data from sheet Data.
On Report sheet, you select period, and you get according report.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Andy" wrote in message
...
I have about 1200 data points for temperature, taken every hour each day.
The
data is in 3 columns, date, time (0:00-24:00 hrs) and the temp reading for
that hour. I want to put the data into a table with date on the left
column,
and the values 0:00 - 24:00 across the top. This will allow me to plot the
data by day, month etc.

I tried a pivot table and couldn't get the data to format the way I needed
it to. Are there other suggestions for automating this task?
Thanks in advance.

Use pivot table option. drag date to the row header and time to the column
header and temp to the data zone.

"Andy" wrote:

I have about 1200 data points for temperature, taken every hour each day. The
data is in 3 columns, date, time (0:00-24:00 hrs) and the temp reading for
that hour. I want to put the data into a table with date on the left column,
and the values 0:00 - 24:00 across the top. This will allow me to plot the
data by day, month etc.

I tried a pivot table and couldn't get the data to format the way I needed
it to. Are there other suggestions for automating this task?
Thanks in advance.