How to copy these cells?

Question

Level 1

97 points

How to copy these cells?

I have the following rows. This is only a small sample. The actual data will go for a year at a time usually.

Date	Time	Open	Close
04/01/2011	8:35 AM	1020.25	1021
04/01/2011	8:40 AM	1021.25	1022
04/01/2011	8:45 AM	1022.25	1023
04/01/2011	8:50 AM	1023.25	1024
04/01/2011	8:55 AM	1024.25	1025
04/01/2011	9:00 AM	1025.25	1026
04/02/2011	8:35 AM	1027.25	1027.5
04/02/2011	8:40 AM	1028.25	1028.5
04/02/2011	8:45 AM	1029.25	1029.5
04/02/2011	8:50 AM	1030.25	1030.5
04/02/2011	8:55 AM	1031.25	1031.5
04/02/2011	9:00 AM	1032.25	1032.5
04/03/2011	8:35 AM	1031.75	1032
04/03/2011	8:40 AM	1031.25	1031.5
04/03/2011	8:45 AM	1030.75	1031
04/03/2011	8:50 AM	1030.25	1030.5
04/03/2011	8:55 AM	1029.75	1030
04/03/2011	9:00 AM	1029.25	1029.5

I'd like to copy out 8:35/open and 9:00/close into new columns along with date. Time isn't relevant in the new columns. Below I show how this should look.

Date	Time	Open	Close	Date	Open	Close
04/01/2011	8:35 AM	1020.25	1021	04/01/2011	1020.25	1026
04/01/2011	8:40 AM	1021.25	1022	04/02/2011	1027.25	1032.50
04/01/2011	8:45 AM	1022.25	1023	04/03/2011	1031.75	1029.25
04/01/2011	8:50 AM	1023.25	1024
04/01/2011	8:55 AM	1024.25	1025
04/01/2011	9:00 AM	1025.25	1026
04/02/2011	8:35 AM	1027.25	1027.5
04/02/2011	8:40 AM	1028.25	1028.5
04/02/2011	8:45 AM	1029.25	1029.5
04/02/2011	8:50 AM	1030.25	1030.5
04/02/2011	8:55 AM	1031.25	1031.5
04/02/2011	9:00 AM	1032.25	1032.5
04/03/2011	8:35 AM	1031.75	1032
04/03/2011	8:40 AM	1031.25	1031.5
04/03/2011	8:45 AM	1030.75	1031
04/03/2011	8:50 AM	1030.25	1030.5
04/03/2011	8:55 AM	1029.75	1030
04/03/2011	9:00 AM	1029.25	1029.5

How can this be accomplished?

Posted on Jun 2, 2011 7:13 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Jun 2, 2011 8:19 PM in response to 4thSpace

If I'm understanding the example, you have:

Date and Time values in column A, formatted to show Date only.

Date and Time values in column B, formatted to show Time only.

Number values in column C.

Number values in column D.

Column E is empty.

You want:

F2 to display the date part of the earliest Time and Date value in column A

F3 to display a date one day later than the date displayed in F2

etc.

G2 to display the 8:35 AM Open value for the date shown in F2

G3 to display the 8:35 AM Open value for the date shown in F3

etc.

H2 to display the 9:00 AM Close value for the date shown in F2

H3 to display the 9:00 AM Close value for the date shown in F3

Time and Date value in column A

Here's one way to go about it.

Column A contains Date and Time values, starting with the D&T shown in A2, then incrementing by five minutes until 9:00 AM is reached in row 7. Enter the formula below in A3, and fill it down to A7.

=A2+1/(24*12)

A8 is one day later than A2:

=A2+1

Set A8 (and A2) to Bold to mark the start of a new day.

Set the cell format for these cells to Date & Time, Date formatted as you choose, Time set to None. (alternately, you can display the full Date and Time value in this column and eliminate column B)

Now select cells A3 to A8, then drag the Fill control down to fill the rest of the column using the same set of formulas (and the Bold setting for the first of each new day).

Column B:

B2: =A

Fill down.

Columns C and D contain entered data. Rather than meticulously copy your sample data, I've used O and strings of os to marl the Open cells to be returned, and C (cc) to mark the Close cells.

F2, G2 and H2 contain variations of the same OFFSET formula, filled down (to the row showing error triangles):

F2: =OFFSET($A$1,(ROW()-2)*6+1,0)

G2: =OFFSET($A$1,(ROW()-2)*6+1,2)

H2: =OFFSET($A$1,(ROW()-2)*6+6,3)

Select all three and Fill down.

The error triangles are a 'bad reference error' caused by the Row offset ( ROW()-2)*6+1 ) pointing to a row not on the table.

Regards,

Barry

Reply

Answer 2

4thSpace Author

Level 1

97 points

Jun 2, 2011 9:17 PM in response to Barry

Perfect Barry. Works great. Thank you.

Reply