PDA

View Full Version : Looping to find difference in time within string



broman5000
01-20-2017, 12:35 PM
Hey guys,

Need some help here.

An example of columns A-D is below and the code below works perfectly...but I'm having issues with this additional ask from my boss

If I loop through each cell in column B and compare it to the entire column A, which the code does...then it finds a match or no match(Great)

Now in column D I would like to display difference in time for those that do not match...If it's easier to write the code to include a zero for the matches, then that is fine too.

Essentially I need to find the difference between the last 5 string's in column b and a (b-a)...example on row 1 would have D2 = -30 minutes

I hope this makes sense. I have attached the file as well.

Thanks for any help!




Master Unique Indicator As Of P201702

Weekly Run Unique Identifier

Weekly Match To Master?

Difference in Master To Current Week Schedule



M3723240111:00

M3723240110:30





R4023240112:00

R4023240111:30





M35232402 8:30

M35232402 8:30

match




R3423240211:00

R32232402 8:30





M2823250115:30

T2423250112:00

match




T2423250112:00

F0323250112:00





F3123250112:00

M36232601 8:30

match




M36232601 8:30

R3723260110:00

match




R3723260110:00

W4323330111:30

match




W4323330111:30

S4423330111:30

match




S4423330111:30

M2023330210:30

match




M2023330210:30

R2023330210:00

match




R2023330210:00

M88234601 8:00

match




M88234601 8:00

R8123460110:30

match




R8123460110:30

M8923460212:00

match




M8923460212:00

R8923460212:00

match




R8923460212:00

M1623460312:30

match




M1623460312:30

R1523460312:30








Sub routechange2()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Long, y As Long, i As Long, alr As Long, blr As Long, clr As Long, Z As Long, C_output As Variant, Threechars As String


Sheets("Unique Identifier list").Select
alr = Sheets("Unique Identifier list").Cells(Rows.Count, "A").End(xlUp).Row
blr = Sheets("Unique Identifier list").Cells(Rows.Count, "B").End(xlUp).Row
clr = Application.Max(alr, blr)

To_Be_Compared = Range("B1:B" & blr)
CompareRange = Range("A1:A" & alr)
' Define and intialise output array
C_output = Range("b1:C" & clr)
For i = 2 To clr
C_output(i, 1) = ""
C_output(i, 2) = ""
Next i

Z = 2

For x = 2 To blr
'check if first three characters are PNP if so skip
Threechars = Left(To_Be_Compared(x, 1), 3)
If Not (Threechars = "PNP") Then
C_output(Z, 1) = To_Be_Compared(x, 1)
Z = Z + 1
For y = 2 To alr
If To_Be_Compared(x, 1) = CompareRange(y, 1) Then
C_output(Z - 1, 2) = "match"
End If
Next y
End If
Next x


For x = 2 To Z - 1
If C_output(Z, 2) = "" Then
If Not (C_output(Z, 1) = "") Then
C_output(Z, 2) = ""
End If
End If
Next x

Range(Cells(1, 2), Cells(clr, 3)) = C_output

Range("C1").Value = "Weekly Match To Master?"
End Sub

SamT
01-20-2017, 05:22 PM
'For each Cel in Column C
If Cel = "" Then _
Cel.Offset(, 1) = DateDiff("n", Right(Cel.Offset(, -2), 5), _
Right(Cel.Offset(, -1), 5)) & " Minutes"

p45cal
01-21-2017, 07:23 AM
try:
Sub routechange4()
Dim alr As Long, blr As Long, WUIWhole, MUIWhole, i As Long, zz As String, OutputRowCount As Long
Dim OutputIdx As Long, j, k, TM, TW, dT, dTStr As String, suffix As String
Dim WUI(), WUITime()
With Sheets("Unique Identifier list")
alr = .Cells(Rows.Count, "A").End(xlUp).Row
blr = .Cells(Rows.Count, "B").End(xlUp).Row

WUIWhole = .Range("B1:B" & blr)
ReDim WUI(1 To UBound(WUIWhole)) 'single dimension array that will contain Weekly UId without time part
ReDim WUITime(1 To UBound(WUIWhole)) 'single dimension array that will contain only time

MUIWhole = .Range("A1:A" & alr)
ReDim MUI(1 To UBound(MUIWhole)) 'single dimension array that will contain Master UId without time part
ReDim MUITime(1 To UBound(MUIWhole)) 'single dimension array that will contain only time

'Split Master UId into ID and time elements:
For i = 2 To UBound(MUIWhole)
zz = MUIWhole(i, 1)
MUI(i) = Left(zz, Len(zz) - 5)
MUITime(i) = TimeValue(Replace(Right(zz, 5), " ", "0"))
Next i

OutputRowCount = 0 'will contain how many rows necessary for output.
'Split Weekly UId into ID and time elements:
For i = 2 To UBound(WUIWhole)
zz = WUIWhole(i, 1)
If Left(zz, 3) <> "PNP" Then OutputRowCount = OutputRowCount + 1
WUI(i) = Left(zz, Len(zz) - 5)
WUITime(i) = TimeValue(Replace(Right(zz, 5), " ", "0"))
Next i

'Define output array:
ReDim C_output(1 To OutputRowCount, 1 To 3)

OutputIdx = 0 'index used when writing to C_output
For i = 2 To UBound(WUIWhole)
zz = WUIWhole(i, 1)
If Left(zz, 3) <> "PNP" Then
OutputIdx = OutputIdx + 1
C_output(OutputIdx, 1) = zz 'write Weekly UId (including time part) to first column of output
j = Application.Match(zz, MUIWhole, 0) 'try to find whole Weekly UId in Master UId…
If IsError(j) Then '…it's not found so…
k = Application.Match(WUI(i), MUI, 0) '…look for just the Weekly UId (without time part)in the Master UId (without time) array
If IsError(k) Then 'if still can't find it then…
C_output(OutputIdx, 3) = "Not found in Master UI" 'write that to column 3 of the output:
Else 'a UId match was found so calcualte time difference:
'next few lines are verbose but I thought this is the part you'd likely want to change, so I didn't shorten the code.
TM = MUITime(k)
TW = WUITime(i)
dT = TW - TM
dTStr = Format(dT, "hh:mm")
If dT < 0 Then suffix = " early" Else suffix = " late"
dTStr = dTStr & suffix
C_output(OutputIdx, 3) = dTStr 'write to 3rd column of output.
End If
Else 'complete match was found so:
C_output(OutputIdx, 2) = "match" 'write to 2nd column of output.
End If
End If
Next i
.Range("b2").Resize(UBound(C_output), 3).Value = C_output 'write to sheet
End With
End SubWhy not put match/time differences all in one column?

broman5000
01-23-2017, 08:59 AM
code works great except for 1 thing...for whatever reason it stops working at row 2565...any ideas?

we could put it all in one column...just not sure how to do it...any ideas?



Master Unique Indicator As Of P201703
Week P201704
Weekly Match To Master?
Difference in Master To Current Week Schedule




M4251058812:00
F0296911013:00
match



R26510588 7:30
T2096911110:30
match



T46510589 7:00
F2096911110:30
match



F44510589 6:45
T01969305 9:00
match



M52511310 3:15
F0196930510:15
match



R40511310 5:00
T06969709 9:15
match



M30511311 8:00
F06969709 9:15
match



R28511311 7:00
F04713918 9:30




M52511312 9:45
T26713919 8:45




R4051131218:45
F15713919 7:00




M52511313 2:00
T27713920 9:00




R40511313 3:30
F27713920 7:30

broman5000
01-23-2017, 09:09 AM
nevermind it works perfectly!

p45cal
01-23-2017, 02:18 PM
we could put it all in one column...just not sure how to do it...any ideas?
Sub routechange5()
Macro7
Dim alr As Long, blr As Long, WUIWhole, MUIWhole, i As Long, zz As String, OutputRowCount As Long
Dim OutputIdx As Long, j, k, TM, TW, dT, dTStr As String, suffix As String
Dim WUI(), WUITime()
With Sheets("Unique Identifier list")
alr = .Cells(Rows.Count, "A").End(xlUp).Row
blr = .Cells(Rows.Count, "B").End(xlUp).Row

WUIWhole = .Range("B1:B" & blr)
ReDim WUI(1 To UBound(WUIWhole)) 'single dimension array that will contain Weekly UId without time part
ReDim WUITime(1 To UBound(WUIWhole)) 'single dimension array that will contain only time

MUIWhole = .Range("A1:A" & alr)
ReDim MUI(1 To UBound(MUIWhole)) 'single dimension array that will contain Master UId without time part
ReDim MUITime(1 To UBound(MUIWhole)) 'single dimension array that will contain only time

'Split Master UId into ID and time elements:
For i = 2 To UBound(MUIWhole)
zz = MUIWhole(i, 1)
MUI(i) = Left(zz, Len(zz) - 5)
MUITime(i) = TimeValue(Replace(Right(zz, 5), " ", "0"))
Next i

OutputRowCount = 0 'will contain how many rows necessary for output.
'Split Weekly UId into ID and time elements:
For i = 2 To UBound(WUIWhole)
zz = WUIWhole(i, 1)
If Left(zz, 3) <> "PNP" Then OutputRowCount = OutputRowCount + 1
WUI(i) = Left(zz, Len(zz) - 5)
WUITime(i) = TimeValue(Replace(Right(zz, 5), " ", "0"))
Next i

'Define output array:
ReDim C_output(1 To OutputRowCount, 1 To 2)

OutputIdx = 0 'index used when writing to C_output
For i = 2 To UBound(WUIWhole)
zz = WUIWhole(i, 1)
If Left(zz, 3) <> "PNP" Then
OutputIdx = OutputIdx + 1
C_output(OutputIdx, 1) = zz 'write Weekly UId (including time part) to first column of output
j = Application.Match(zz, MUIWhole, 0) 'try to find whole Weekly UId in Master UId…
If IsError(j) Then '…it's not found so…
k = Application.Match(WUI(i), MUI, 0) '…look for just the Weekly UId (without time part)in the Master UId (without time) array
If IsError(k) Then 'if still can't find it then…
C_output(OutputIdx, 2) = "Not found in Master UI" 'write that to column 2 of the output:
Else 'a UId match was found so calcualte time difference:
'next few lines are verbose but I thought this is the part you'd likely want to change, so I didn't shorten the code.
TM = MUITime(k)
TW = WUITime(i)
dT = TW - TM
dTStr = Format(dT, "hh:mm")
If dT < 0 Then suffix = " early" Else suffix = " late"
dTStr = dTStr & suffix
C_output(OutputIdx, 2) = dTStr 'write to 2nd column of output.
End If
Else 'complete match was found so:
C_output(OutputIdx, 2) = "match" 'write to 2nd column of output.
End If
End If
Next i
.Range("b2").Resize(UBound(C_output), 2).Value = C_output 'write to sheet
End With
End Sub