Place values in sheet 1 based on conditions in sheet 2

[abberyfarm]

Hi there,

I am hoping somebody here could help me out with a macro to do a task that I have

In sheet 1, I have time values (Col A) and speed values (Col B).

In sheet 2, I have a start time (Col A), an end time (Col B) and in column C either numbers 1 and 2 which indicate a particular type of data.

Here is an example of sheet 1

      Time [sec]	 Speed
14/04/2011 07:40:35	 0
14/04/2011 07:40:36	 1
14/04/2011 07:40:37	 3
14/04/2011 07:40:38	 4
14/04/2011 07:40:39	 5
14/04/2011 07:40:40	 7
14/04/2011 07:40:41	 8
14/04/2011 07:40:42	 9
14/04/2011 07:40:43	 10
14/04/2011 07:40:44	 10
14/04/2011 07:40:45	 11
14/04/2011 07:40:46	 12
14/04/2011 07:40:47	 12
14/04/2011 07:40:48	 13
14/04/2011 07:40:49	 14
14/04/2011 07:40:50	 15
14/04/2011 07:40:51	 3.8
14/04/2011 07:40:52	 7.5

And of sheet 2

Start Time [sec]	   End Time [sec]       Type
14/04/2011 07:40:35	14/04/2011 07:40:39	 1
14/04/2011 07:40:40	14/04/2011 07:40:46	 2
14/04/2011 07:40:47	14/04/2011 07:42:07	 1
14/04/2011 07:42:08	14/04/2011 07:42:38	 1
14/04/2011 07:42:39	14/04/2011 07:43:09	 2
14/04/2011 07:43:10	14/04/2011 07:43:40	 2
14/04/2011 07:43:41	14/04/2011 07:44:11	 2
14/04/2011 07:44:12	14/04/2011 07:44:42	 2
14/04/2011 07:44:43	14/04/2011 07:45:13	 1
14/04/2011 07:45:14	14/04/2011 07:45:44	 1
14/04/2011 07:45:45	14/04/2011 07:46:15	 2
14/04/2011 07:46:16	14/04/2011 07:46:46	 1
14/04/2011 07:46:47	14/04/2011 07:47:17	 2
14/04/2011 07:47:18	14/04/2011 07:47:48	 2
14/04/2011 07:47:49	14/04/2011 07:48:19	 1
14/04/2011 07:48:20	14/04/2011 07:48:50	 1
14/04/2011 07:48:51	14/04/2011 07:49:21	 1

What I would like to do is,

In sheet 2, check the value in each row of column C. If the value equals 1, then go back to sheet 1 and put the number one in in every row in column c between the two times. And do the same if the value equals 2.

So sheet one would like this after.

      Time [sec]	 Speed
14/04/2011 07:40:35	 0       1
14/04/2011 07:40:36	 1       1 
14/04/2011 07:40:37	 3       1
14/04/2011 07:40:38	 4       1
14/04/2011 07:40:39	 5       1
14/04/2011 07:40:40	 7       2
14/04/2011 07:40:41	 8       2
14/04/2011 07:40:42	 9       2
14/04/2011 07:40:43	 10      2
14/04/2011 07:40:44	 10      2
14/04/2011 07:40:45	 11      2
14/04/2011 07:40:46	 12      2
14/04/2011 07:40:47	 12      1
14/04/2011 07:40:48	 13      1
14/04/2011 07:40:49	 14      1
14/04/2011 07:40:50	 15      1
14/04/2011 07:40:51	 3.8     1
14/04/2011 07:40:52	 7.5     1

Thank you in advance

 

[rassten]

Hi, maybe something like this ( I know it is not a macro, but it can be done with a formula)

On Sheet1, cell c2 =VLOOKUP(A2,Sheet2!$A$1:$C$18,3,TRUE)

 

[abberyfarm]

Hi thanks for the reply, that does what I want to do cheers