Automatically copy data based on date and time

[Guest]

I have a list of data that measures deviation from a starting value
throughout the day on 15 minute intervals. I need to separate the data in
that daily range and copy into another sheet.

The data looks like this:

7/26/2006 4:15 3
7/26/2006 4:30 -4
7/26/2006 5:00 4
7/26/2006 7:45 6
7/26/2006 12:00 17
7/26/2006 15:15 29
7/26/2006 19:15 53
7/26/2006 19:45 59
7/26/2006 22:30 56
7/26/2006 23:00 44
7/26/2006 23:30 52
7/26/2006 23:45 44
7/27/2006 3:00 50
7/27/2006 3:45 56
7/27/2006 4:00 -5
7/27/2006 4:45 -11
7/27/2006 5:15 -11
7/27/2006 6:00 5

I need to pull data from each day starting at 4:00 through 3:45 the next
day, placing the data into rows, not columns with a new row for each day. I
have no idea where to start.

 

[Pete_UK]

It's not clear if your date and time are combined in one column or if
they occupy two separate columns. If they are in separate columns then
you could apply autofilter on the date column for the day you want and
apply another filter on the time - Custom | Greater than or equal to |
4:00. Then highlight all the visible data and click <copy>, click over
to your other sheet and position the cursor where you want the first
item of data and Edit | Paste Special | Transpose (check) then OK
followed by <Esc>. Repeat this for the next day with time Less than
4:00, and butt that up to the end of the data you have just pasted.

If your date and time are combined in one column, then you can apply a
Custom filter to this column - Greater than or equal to <date> 4:00
AND Less than <date+1> 4:00. Then copy and transpose as before, but
you can do it all in one operation.

Hope this helps.

Pete

 

[Guest]

The time info is in one column and your post is helpful. However I have 2
years worth of data that I need to sort for 9 dfferent worksheets, so to
manually filter is very time consuming. Is there anything you can suggest to
automatically copy and paste, or create a table that separates each day out
into a separate row or column?

Thanks again for your post and any more help you can give.

 

[Pete_UK]

You could record a macro while you do the copy/paste a few times, and
then if you examine the code you will be able to see the similarities
and differences. If you did it five times, say, then you will notice 5
different dates specified as filter criteria, so this might help you
to see how you could edit the recorded macro by, for example, taking a
date from a cell somewhere and then repeating the filtering for 5
consecutive days (or 10, or some other multiple).

If you expect to generate 9 different worksheets from 2 years' worth
of data, then I suppose you are splitting it into quarters (90 days?).
You could create the blank sheets yourself and then edit the code to
do 90 days into one sheet, and then just edit the sheet name for the
next 90 days.

It depends how proficient you are with VBA, but I think a macro is the
only way to achieve what you want given the number of days that you
have.

Hope this helps.

Pete