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.

 

[mayday1]

Have I stumped the panel or does the question not make sense?

 

[DanteAmor]

Put the following time format in file1 and file2

<b>File1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:114.06px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">06:00:00 a.m.</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">06:01:00 a.m.</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">06:02:00 a.m.</td><td style="text-align:right; ">37</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">06:03:00 a.m.</td><td style="text-align:right; ">37</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">06:04:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">06:05:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">06:06:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">06:07:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">06:08:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">06:09:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">06:10:00 a.m.</td><td style="text-align:right; ">12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">06:11:00 a.m.</td><td style="text-align:right; ">12</td></tr></table>

---

<b>File2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:119.76px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">06:02:00 a.m.</td><td style="text-align:right; ">37</td><td style="text-align:right; ">00:02:12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">06:04:00 a.m.</td><td style="text-align:right; ">12</td><td style="text-align:right; ">00:20:18</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">06:24:00 a.m.</td><td style="text-align:right; ">37</td><td style="text-align:right; ">00:01:00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">07:24:00 a.m.</td><td style="text-align:right; ">12</td><td style="text-align:right; ">00:01:00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">08:25:00 a.m.</td><td style="text-align:right; ">16</td><td style="text-align:right; ">01:12:00</td></tr></table>

---
You can put the macro in another book, you must have open the file1 and file2

Sub Getting_data()
    Dim l1 As Workbook, l2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, f As Range, valor As Variant
    Dim i As Long, wEvent As Variant, wHours As Variant, wMinut As Variant
    
    Set l1 = Workbooks("File1.xlsx")
    Set l2 = Workbooks("File2.xlsx")
    
    Set sh1 = l1.Sheets(1)
    Set sh2 = l2.Sheets(1)
    
    sh1.Range("B:B").ClearContents
    
    For Each c In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))
        wEvent = c.Offset(0, 1).Value
        wHours = Val(Format(c.Offset(0, 2).Value, "hh")) * 60
        wMinut = Val(Right(Format(c.Offset(0, 2).Value, "hh:mm"), 2)) + wHours
        valor = TimeValue(Format(c.Value, "hh:mm")) & ""
        Set f = sh1.Range("A:A").Find(valor, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            For i = 0 To wMinut - 1
                f.Offset(i, 1).Value = wEvent
            Next
        End If
    Next
    MsgBox "End"
End Sub

 

[mayday1]

I get Run-time error '13': Type mismatch on valor = TimeValue(Format(c.Value, "hh:mm")) & ""

I'm not sure what this line is intended to do.

 

[Twollaston]

put in File 1 column B:
=VLOOKUP(A1,File2!A:B,2,TRUE)

[TABLE="width: 189"]
<tbody>[TR]
[TD="colspan: 2"]Sheet 1[/TD]
[/TR]
[TR]
[TD="align: right"]6:00:00 AM[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]6:01:00 AM[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]6:02:00 AM[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="align: right"]6:03:00 AM[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="align: right"]6:04:00 AM[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]6:05:00 AM[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 287"]
<tbody>[TR]
[TD="colspan: 3"]Sheet 2[/TD]
[/TR]
[TR]
[TD="align: right"]6:02:00 AM[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6:04:00 AM[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6:24:00 AM[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7:24:00 AM[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

I get Run-time error '13': Type mismatch on valor = TimeValue(Format(c.Value, "hh:mm")) & ""

I'm not sure what this line is intended to do.

Transform the value of your cell to a value of hour: minute, but in the cell you must have time values, as shown in my examples.

If you have other information, you could put here.

 

[mayday1]

I thought about somehow using a VLOOKUP but that would only get me the start time of each event. I'm going through the code DanteAmor sent and I think he's got it except for the little glitch I got when I ran it. His flow looks right but I don't understand all of his detail yet.

 

[Twollaston]

I thought about somehow using a VLOOKUP but that would only get me the start time of each event. I'm going through the code DanteAmor sent and I think he's got it except for the little glitch I got when I ran it. His flow looks right but I don't understand all of his detail yet.

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.

 

[DanteAmor]

I thought about somehow using a VLOOKUP but that would only get me the start time of each event. I'm going through the code DanteAmor sent and I think he's got it except for the little glitch I got when I ran it. His flow looks right but I don't understand all of his detail yet.

According to you, the formula would only find the beginning but not the end of the time of the event. If the event lasted 2 minutes, the formula (with true) would respond to all times and we only need 2.

I explain a bit each line of the code and the problem of working with time, if it were a text or a number would be simpler.

Sub Getting_data()
    Dim l1 As Workbook, l2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, f As Range, valor As Variant
    Dim i As Long, wEvent As Variant, wHours As Variant, wMinut As Variant
    
    Set l1 = Workbooks("File1.xlsx")
    Set l2 = Workbooks("File2.xlsx")
    
    Set sh1 = l1.Sheets(1)
    Set sh2 = l2.Sheets(1)
    
    sh1.Range("B:B").ClearContents
    
    For Each c In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))

       [COLOR=#0000ff] 'For each hour of your sheet, I get the event number[/COLOR]
        wEvent = c.Offset(0, 1).Value

        [COLOR=#0000ff]'I get the number of hours, the event can last 1 minute or several hours, 
        'then I get the number of hours of duration of the event[/COLOR]
        wHours = Val(Format(c.Offset(0, 2).Value, "hh")) * 60


        [COLOR=#0000ff]'I get the number of minutes  plus the number of minutes in the hours[/COLOR]
        wMinut = Val(Right(Format(c.Offset(0, 2).Value, "hh:mm"), 2)) + wHours

       [COLOR=#0000ff] 'I convert the value of the cell to hour: minute to perform the search on sheet1, 
        'so the importance of the format on sheet1 is as shown in my example.[/COLOR]
        valor = TimeValue(Format(c.Value, "hh:mm")) & ""
        
        [COLOR=#0000ff]'Find[/COLOR]
        Set f = sh1.Range("A:A").Find(valor, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            [COLOR=#0000ff]'If it finds the minute
            'from that minute to the total of the duration it puts the event number in column B of the sheet1[/COLOR]
            For i = 0 To wMinut - 1
                f.Offset(i, 1).Value = wEvent
            Next
        End If
    Next
    MsgBox "End"
End Sub

 

[Twollaston]

According to you, the formula would only find the beginning but not the end of the time of the event. If the event lasted 2 minutes, the formula (with true) would respond to all times and we only need 2.

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?