Doing a search and copying a command

[libertyforall]

I figured out how to import data. Now I am trying to figure out a
couple of things. I have a column of dates. A range of 48 cells of
1/1/09 in column A. Each row is data for each half hour. Now that
range of 48 cells is the repeated for each day of the year. I can
easily do a command manually for each day but that will require me to
do 365 seperate commands. I would like to find the minimum temperature
within 2 columns of F & G for only rows in Column A that have 1/1/09.
I wanted to do something like using a search/lookup/find command for
1/1/09 min F:G. Then do the same thing for 1/2/09. Then if I can do
that, is it possible to select the two the cells and drag it down to
increase the command by one day until the last day of the year?

 

[libertyforall]

   I figured out how to import data. Now I am trying to figure out a
couple of things. I have a column of dates. A range of 48 cells of
1/1/09 in column A. Each row is data for each half hour. Now that
range of 48 cells is the repeated for each day of the year. I can
easily do a command manually for each day but that will require me to
do 365 seperate commands. I would like to find the minimum temperature
within 2 columns of F & G for only rows in Column A that have 1/1/09.
I wanted to do something like using a search/lookup/find command for
1/1/09 min F:G. Then do the same thing for 1/2/09. Then if I can do
that, is it possible to select the two the cells and drag it down to
increase the command by one day until the last day of the year?

Does anyone have an idea about this?

 

[Dave Peterson]

I put headers in row 1 and test data in:

A2:a17521
(365*48 rows)

Then I could use an array formula like this to find the minimum of column F for
January 1, 2009.

=MIN(IF(TEXT(A2:A17521,"yyyymmdd")="20090101",F2:F17521))

But doing this 365 * 2 (columns) seems like way too much work.

Instead, you could use a pivottable.

Select your range (A1:G17521 for me)
Data|pivottable (xl2003 menus)

Follow the wizard until you see the dialog with a Layout button on it.
Click that Layout button

Drag the header for column A (the date) to the row field.

Drag the header for column F to the data field.
Double click on that header and change it to "Min of" -- "sum of" or "count of"

Same thing for column G

Finish the wizard to a new worksheet.

Now drag the Data grey icon right on top of the cell with Total on it.
See Debra Dalgleish's video if you don't understand:
http://contextures.com/xlVideo001.html

This gives you a pivottable for each time. So you're not quite done.

Now rightclick on the grey header for the date and choose:
Group and show detail, then Group.
Choose Days, Months, and years

And finish up.

If you get a message that this field cannot be grouped, then you have some
non-numeric stuff in your data field. That could be plain old text or empty
cells. Fix those and try again.

You'll end up with something that looks like:

Data
Years Months COLA Min of COLF Min of COLG
2009 Jan 01-Jan 1632 4182
02-Jan 226 1842
03-Jan 7 270
04-Jan 866 2277
05-Jan 320 524
06-Jan 1677 1619
07-Jan 621 583
08-Jan 338 263
09-Jan 84 1713
10-Jan 1481 46
11-Jan 457 1162
12-Jan 70 2990
.....


Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Dave Peterson]

ps. I put this in A2:

=DATE(2009,1,1)+((ROW()-2)/(24*2))

Gave it a nice format: mm/dd/yyyy hh:mm:ss
and copied down to row 17521
to create my test data

<<snipped>>

 

[Dave Peterson]

pps. Just more info.

=MIN(IF(TEXT(A2:A17521,"yyyymmdd")="20090101",F2:F17521))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

<<snipped>>

 

[libertyforall]

ps.  I put this in A2:

=DATE(2009,1,1)+((ROW()-2)/(24*2))

Gave it a nice format:  mm/dd/yyyy hh:mm:ss
and copied down to row 17521
to create my test data

<<snipped>>

Hurray! I did it. I was able to use the pivot table to get the data!
It was simple!