Doing a search and copying a command

   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?
0
roy4059 (18)
4/15/2009 7:05:23 AM
excel 39879 articles. 2 followers. Follow

5 Replies
932 Views

Similar Articles

[PageSpeed] 11

On Apr 14, 9:05=A0pm, libertyforall <r...@royhuff.com> wrote:
> =A0 =A0I 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?
0
roy4059 (18)
4/18/2009 2:36:14 AM
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






libertyforall wrote:
> 
>    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?

-- 

Dave Peterson
0
petersod (12005)
4/18/2009 12:41:37 PM
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>>
0
petersod (12005)
4/18/2009 12:45:33 PM
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>>
0
petersod (12005)
4/18/2009 12:53:49 PM
On Apr 18, 2:45=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> ps. =A0I put this in A2:
>
> =3DDATE(2009,1,1)+((ROW()-2)/(24*2))
>
> Gave it a nice format: =A0mm/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!
0
roy4059 (18)
4/19/2009 8:01:08 AM
Reply:

Similar Artilces:

Copy my contacts to disc on a disc in Outlook 2003
I want to copy my e-mail contacts to a disc. I have Outlook 2003. I found out a way to copy to a xls file,but it comes out with too many columns. I would rather just copy it the way it is my contact folder, alphabetically or the way it is in my address book. Your Contacts are not separate from the rest of your Outlook data, so you would copy your entire data file. The file you need is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup or transfer: http://www.slipstick.com...

search for right
Hi all, I wonder if there's any possibility to do the following without rearranging my tables: I'd like to look up an order number in a table, and get the price from two coloumns _left_ So the order number is in C3 and I'd like to get the value in A3 and so on. Vlookup does only to right... Please advise, Thanks, Marton =INDEX(A1:A100,MATCH(ord_num,C1:C100,0)) -- HTH RP (remove nothere from the email address if mailing direct) "VilMarci" <dontsend@here.com> wrote in message news:eHlKiP6VFHA.3764@TK2MSFTNGP15.phx.gbl... > Hi all, > > I wonder if...

Excel 2007
I am using Office 2007 Beta 2 version and I am having problems with Excel. I am unable to copy a sheet with a chart in it. Below is the procedure: 1. Create new book 2. right-click sheet 1 tab 3. Insert , then insert a chart 4. right-click chart 1 tab 5. choose move or copy... 6. copy chart 1 to the end of this work book. I cannot create a copy of the chart. Is this considered a bug in Excel 2007? If it is, do you know any links to sites that I may use as references? Thank you! Lots of bugs in Beta 2 especially with charts, I'm told If its any consolation, I cannot copy a chart ...

copying to another spreadsheet
I have a table in spreadsheet x of 10 columns and 1000 rows. Afte sorting by the entries in column a, I would like to copy row entrie with the same column a entry to a specific location in spreadsheet Y. E.g., the rows that have "oranges" in column a in spreadsheet x woul be copied to a specific location in spreadsheet Y, under a heading o "oranges". In the same vein, column a entries in spreadsheet x o "pears" would be copied to spreadsheet y under the heading of "pears". And so on. Is there any way to automate the copying of rows fro spreadsheet x ...

How do I exlude hidden cells when copying data
I have a spreadsheet with some hidden columns. I want to copy and paste only the columns that I can see. How do I achieve this. Bully, First select your range. Edit - Go to - Special - Visible Cells only. Now copy and paste. -- Earl Kiosterud "Bully Ram" <Bully Ram@discussions.microsoft.com> wrote in message news:493C4EF2-4A7A-41F3-91EE-D49EE9D17FB8@microsoft.com... >I have a spreadsheet with some hidden columns. I want to copy and paste >only > the columns that I can see. How do I achieve this. ...

Ctrl- F for search
I have a search button on the form which works fine. I tried to use Ctrl-F to do the same function and get "an error occurred in a call to the Windows Date and Time Picker control" I have DateTimePicker control on the form. Are there any conflict with Ctrl-F search function and DateTimePicker ActiveX control? Your information is great appreciated, ...

Not saving records till "save" command button clicked
If you are in a form and adding information, then close out or go to a new for it just saves the record as long as all applicable fields are filled out. Is there a way to only have a record save if a command button is clicked? -- Message posted via http://www.accessmonster.com If you simply hide the form instead of closing it, I think that will prevent the save. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "szag via AccessMonster.com" <u2885@uwe> wrote in message news:a75249f4b37bb@uwe... > If you are in a form and adding informat...

How to copy block of cells and keep grouping?
I have about a 150 by 30 block of cells. About every 5 cells are grouped together, making it significantly smaller. when you click the "-" to have them contract. I will take me days to go through and make all the groups again if I want to make the same block on the same worksheet. Is there a way to copy a block of cells and include the grouping? Thanks -- dstock ------------------------------------------------------------------------ dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24225 View this thread: http://www.excelforum.com/showthre...

Copying dates between Excel documents
I have noticed that when I copy a date eg/ 23/02/04 (UK format) from on Excel document to another the date becomes 22/02/00. Has anyone comes across this, its really infuriating as I have t redate everything again Thank -- Message posted from http://www.ExcelForum.com XL has two date systems, the Windows default 1900 date system, in which the 0-base date is 31 December 1899, and the MacXL default 1904 date system, in which the 0-base date is 1 January 1904. The systems differ by 4 years and a day. To resolve your issue, make sure that both workbooks have the same date system selected...

search glitch \ problem Outlook 2007
I have one contact with many telephone numbers. One number was entered in under 'other' in the phone number section of that contact. When doing a seach for that 'other' number, nothing shows up. Is 'other' automatically excluded from the search function? (Outlook 2007, Vista HP, SP2) Thanks, Dave Horne Hello Dave, I tried it myself... And I managed to get an 'other' phone number. However, I'm using Lookeen as my preferred search tool. With the 'normal' Outlook search I couldn't get it displayed either... Probably you can get...

Outlook 2007: Disable Instant Search
I keep getting notified when I launch Outlook 2007 to download a program to enable instant search. Is there a way to disable this? See http://www.slipstick.com/emo/2006/up060525.htm#search --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <ccheviron@gmail.com> wrote in message = news:1148958039.306539.254390@j55g2000cwa.googlegroups.com... >I keep g...

Exchng 2k Message Tracking Center; No Facility Search Available
I have a standard setup 2k box and am having problems with the message tracking center, the tracking is working fine in the sense of creating logs. I am unable to use MMC to search or view the message in the right hand pane of MMC. The Message center appears in the tools section on the left; double clicking or right hand clicking has no effect and the track message option is not there!! I have checked the logs and they seem fine and are logging correctly. As any one seen this before or can offer any solution? Many Thanks in advance. ...

copy and paste
I have to copy and paste a bunch of stuff from my Excel spreadsheet to my Word....but I can't copy several lines without copying the grids too, or at least the words will be out of sequence....(I just want all the words, not the table setup) does anyone know how to fix this? help will be greatly appreciated, since this could help me save so much time. plz email me with replies thanks! --linda Copy the data in Excel, and paste it into Word In Word, select the table that contains the data Choose Table>Convert>Table to Text Select one of the separation options, click OK Linda w...

copy data from one worksheet to identical sheet in different workbook
I want to essentially sync the data between 2 identical worksheets in different workbooks. Essentially, I want to import and replace data in the second sheet with data from the first, is there a simple way to do this without deleting the worksheet and then copying the new sheet into the workbook? Thanks You can right-click on your source-sheetname tab, the shortmenu will reflect Copy/MoveSheet.... Select it and make 2 other choices: 1) Check to Create Copy box at bottom 2) From dropdown box at top of screen select an existing open workbook or select new-workbook. OK HTH "a...

Searching in a column of strings
Hi I try to find how many cells have a sequence of charcters. From Cell A1:A50. Exemple for "NY" : cellA1 = NY123 cellA2 = NY3235 cellA3 = NY5434 cellA4 = LA4234 function(NY) = 3 Like Seach but instead to be in a single cell, it's in a sequence of cell? Thanks Jack Jack =COUNTIF($A:$A,"*NY*") Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:348501c3fd71$700c6c60$a001280a@phx.gbl... > > Hi > > I try to find how many cells have a sequence of > charcters. From Cell A1:A50. > > Exe...

add to Runas command
I tried adding some more functionality to the batch file below with no success. Is there a way that I can add to this batch file? For example, color 1f and loading some macros would be great too. Thanks. runas.exe /user:Administrator cmd.exe Below is an example batch file (incorporating the command you quoted ) I have working on my system to add some helpful stuff to a new "cmd" window.... *NB Make sure the batch-file is named ADMCMD.BAT and is in the system's "path" (i.e.; in a folder included in the PATH variable, usually C:\WINDOWS is one) and...

502 Command unimplemented
Howdy, I am having trouble with my Exchange server sending to only some domains. Mainly Hotmail and MSN.. but there are a few others. Some email goes through just fine to other domains. I DO know for sure I can't send to hotmail or MSN. Anyway, the undeliverable message I get back says: There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <mydomain.com #5.5.0 smtp;502 Command unimplemented> Any clues on what I should do? I am stummped. I can find some info on the error message but no remedy as to how to ...

2003: Copy R-Click Option Missing!
In outlook 2003 when i right-click on an email message, only the move command/option is there (amongst others), but the copy command is missing? the only way I can copy is to drag the message to a folder with the right mouse button depressed and then the copy option shows up. Why is the copy option missing when I right click on an email message? thank you ...

copy record between tables
Hi, I have two tables with same structure. I need to copy a record from oneto other using vba codes. I'd like avoid to copy field to field.Thank you. Without knowing anything about your table structures, something similar to the following:Dim strSQL As StringstrSQL = "INSERT INTO tblCopy SELECT tblOriginal.* " _ & "FROM tblOriginal WHERE tblOriginal.KeyValue = 4"CurrentProject.Connection.Execute strSQL"Alberto" <saveas[at]iol.it> wrote in message news:uk2oUMBYHHA.3656@TK2MSFTNGP05.phx.gbl...> Hi,> I have two tables with same structure....

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Software Licensing and Copy Protection
Hi, I am a CrypKey Programmer, knowledgeable in implemting software copy protection and software licensing solutions. If you are looking for such a solution, then drop me an email. Regards, David crypkeyprogrammer@yahoo.com Could you please tell me some good web resource for learning this. Manish "CrypKey Programmer" <CrypKeyProgrammer@Yahoo.com> wrote in message news:jAXEc.961632$oR5.129388@pd7tw3no... > Hi, > > I am a CrypKey Programmer, knowledgeable in implemting software copy > protection and software licensing solutions. > If you are looking for suc...

Searching a Differnt worksheet in the same workbook.
So what I need to do is have a colum D that searches Sheet1, column E for a certain text string entered in a different sheet (either 2 or 3) column D. If they match I want the cell in column E, sheet 2 or 3 to return a text of "Found" and if there is no match I want the cell to be blank. I've been looking in these forums and have a feeling it can be done, I just can't figure out how. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Dear Cre...

Can Pivot Table layout be copied???
A few weeks ago, I ended up with the perfect Pivot Table layout by dragging fields around -- I just don't remember how I got there! I have attempted to create a similar table (with new data) but, even though the field layout buttons are in the exact same locations, the table looks different. I'm trying to get "Sum of 2004", "Sum of 2005," etc. as column headings across the top instead of having all years one below the other. Is there any way to copy the layout from the "perfect" table to the new one? I answered my own question, with help from De...

Help with copying formula
Hi I run Excel 2K I have typed the following formulas in three consecutive cells. =SUM('DRO BY SHIFT'!Z10:Z12)+BP10 =SUM('DRO BY SHIFT'!Z13:Z15)+BP11 =SUM('DRO BY SHIFT'!Z16:Z18)+BP12 You will note that the Z colum ranges take in 3 cells each time. The problem is that when I drag or copy these formulas down the column I dont get the reltive ranges that I need. That is the next one down should read =SUM('DRO BY SHIFT'!Z19:Z21)+BP13 However I get =SUM('DRO BY SHIFT'!Z17:Z19)+BP13 How can I write the formula so that when I drag it down it maintai...

Hashes appear in cell when copying from another
Hi, I have imported some .txt data into a Excel 2003 spreadsheet. In some instances, I have had to copy the data from one cell below another such that the text makes sense. However, in some instances when I do so, the cell that is being copied to gets filled completely with the hash symbol (#). Initially, I thought that the problem was only due to cell column width but when I expand the width the problem still persists. Doe anyone have any ideas? Instead of copying part of the data to the cell below, try to wrap the txt in one cell. Select the cells, Press Ctrl + 1 Select tab &q...