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
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