Matching
 

Hi
We want to be able to match number plates using excel and creating an
output file that shows the time difference between the two number
plates. The file we get in is like this

Site Vehicle Type Reg Plate Time (Hour) Time (Min)
001 1 T890RFW 07 00
001 1 FL51FYZ 07 00
001 1 KC04PZG 07 00
001 1 P610APB 07 00
001 1 P676JKP 07 00
001 1 FG51OZB 07 00
001 1 P849GAL 07 00
001 1 R787KHP 07 00
001 1 FH52SUD 07 00
001 1 X591BAU 07 00
001 1 P258UVK 07 00
001 1 BS2200 07 01
001 1 R142GWO 07 01
002 1 FH52SUD 07 20
002 1 FG51OZB 07 29

and it needs to come out like this

Entry Place Exit Place Time Number Plate


Any help with this would be greatly appreciated. If more
clarrification is needed pls msg and i will try my best.

Skyhigh


--
Skyhigh
------------------------------------------------------------------------
Skyhigh's Profile: http://www.excelforum.com/member.php...o&userid=24907
View this thread: http://www.excelforum.com/showthread...hreadid=384449

Matching
 

can we assume that the first entry is the 'entry' and the second is the 'exit'?

this should get you started:-

Option Explicit

Sub Report()

Dim ws As Worksheet
Dim source As Range
Dim rw As Long
Set source = Range("A1").CurrentRegion

Set ws = Worksheets.Add
With ws.Range("A1").Resize(source.Rows.Count, 5)
.Value = source.Value
.Sort .Range("C1"), Header:=xlYes
With .Resize(source.Rows.Count - 1, 1).Offset(1, 5)
.FormulaR1C1 = "=CountIf(C3:C3,RC3)"
End With
End With
' remove single entries
For rw = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(rw, 6) = 1 Then
Rows(rw).Delete
End If
Next
' re-format
Columns(2).Insert
For rw = Range("A1").End(xlDown).Row To 3 Step -2
Cells(rw - 1, 2) = Cells(rw, 1)
Cells(rw - 1, 7) = Cells(rw, 5)
Cells(rw - 1, 8) = Cells(rw, 6)
Rows(rw).Delete
Next
End Sub
TODO: add column headers

"Skyhigh" wrote:


Hi
We want to be able to match number plates using excel and creating an
output file that shows the time difference between the two number
plates. The file we get in is like this

Site Vehicle Type Reg Plate Time (Hour) Time (Min)
001 1 T890RFW 07 00
001 1 FL51FYZ 07 00
001 1 KC04PZG 07 00
001 1 P610APB 07 00
001 1 P676JKP 07 00
001 1 FG51OZB 07 00
001 1 P849GAL 07 00
001 1 R787KHP 07 00
001 1 FH52SUD 07 00
001 1 X591BAU 07 00
001 1 P258UVK 07 00
001 1 BS2200 07 01
001 1 R142GWO 07 01
002 1 FH52SUD 07 20
002 1 FG51OZB 07 29

and it needs to come out like this

Entry Place Exit Place Time Number Plate


Any help with this would be greatly appreciated. If more
clarrification is needed pls msg and i will try my best.

Skyhigh


--
Skyhigh
------------------------------------------------------------------------
Skyhigh's Profile: http://www.excelforum.com/member.php...o&userid=24907
View this thread: http://www.excelforum.com/showthread...hreadid=384449

Matching
 

Thanks we will give it a go


--
Skyhigh
------------------------------------------------------------------------
Skyhigh's Profile: http://www.excelforum.com/member.php...o&userid=24907
View this thread: http://www.excelforum.com/showthread...hreadid=384449