Getting data based on time

[mayday1]

I have 2 files.
In File1.xlsx, Column A contains the time of day and Column B is where I need work codes copied from File2.xlsx. So before anything happens, File1.xlsx looks like this
A1 = 06:00
A2 = 06:01
A3 = 06:02
A4 = 06:03
A5 = 06:04
A6 = 06:05
Etc. all day long, 1 row for every minute of the day.

File2.xlsx contains details of things that happen throughout the day in Column B and the time of the event in Column A. Column C shows the duration in hh:mm:ss.
So…if A1, B1 and C1 show…
06:02, 37, 00:02:12
06:04, 12, 00:20:18
06:24, 37, 00:01:00
07:24, 12, 00:01:00
It would tell me Event 37 (37 is Emergency) started at 06:02 and lasted for 2 minutes and 12 seconds. Column D is only populated when an event starts, 1 row of data for every event start time. I need those work codes populated in File1.xlsx for every minute they were active. Seconds can be rounded off.

Here’s what I’m unable to figure out:
Using the example above, I need File1.xlsx Columns A and B to show
06:00
06:01
06:02, 37
06:03, 37
06:04, 12
06:05, 12
06:06, 12 etc until the row with 06:24 which would show 06:24, 37

I'm lost.

 

[DanteAmor]

Yes, From the example it looks like there is always an event running because the duration always match up with the time of the next event next event (Except the rounded seconds)


Just wondering is there a time mid day when no event is running?

Mayday says:

Etc. all day long, 1 row for every minute of the day.

 

[Twollaston]

Mayday says:

I’m referring to:

06:02, 37, 00:02:12
06:04, 12, 00:20:18
06:24, 37, 00:01:00
07:24, 12, 00:01:00

Event 37 and Event 12

Specifically

06:02, 37, 00:02:12
06:04, 12, 00:20:18
06:24, 37, 00:01:00 <—- 1 minute? Or did she mean 1 hour?
07:24, 12, 00:01:00 <—- 1 minute?

So for sheet 1
06:24, 37
06:25, [is this a blank?]
06:26, “”
Etc...
and then
07:24 , 12

To me it looked to be 1 hour instead of 1 minute because the difference in sheet 2 event times are = duration for 6:02,6:04,6:24
For example A2-A1=rounded(c1) and A3-A2=rounded(c2)
But A4-A3 <> rounded(c3)

 

[mayday1]

If you use Exact Values (FALSE) vlookup will only show exact times, if you use Approximate Values (TRUE) excel will place the event number under the all the times earlier than the next event
Glad you were able to get it sorted.

This is one of those things that I'm surprised I didn't know or didn't think to do. Works perfectly - thank you.

 

[mayday1]

The other suggestion takes care of my problem but - you're code will help me out with another question I was having trouble with. Thank you

 

[DanteAmor]

The other suggestion takes care of my problem but - you're code will help me out with another question I was having trouble with. Thank you

Well, that's what publications are for to help this problem and maybe to solve some other problem. Have a nice day and thanks for the feedback.