Getting data based on time

mayday1

mayday1

Board Regular
Joined
Oct 5, 2007
Messages
241
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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]
 
Last edited:
Upvote 0
mayday1 said:
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.
Click to expand...


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.
 
Upvote 0
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.
 
Upvote 0
mayday1 said:
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.
Click to expand...

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. :)
 
Upvote 0
mayday1 said:
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.
Click to expand...

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.
Code: 
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
 
Upvote 0
DanteAmor said:
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.
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
890
Alex Blakenburg
A
H
  • Solved
Vlookup: unexplainable - for me - #N/A
Replies
3
Views
79
Hans Troost
H
R
  • Question Question
Freezing/Logging Data from DDE at 1 Minute Intervals
Replies
1
Views
125
Anthony47
A
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top