Matching

Excel Programming >> 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.hide-link.com/ ;userid=24907
View this thread: http://www.hide-link.com/

Excel109

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

>
> 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?action=getinfo&userid=24907
> View this thread: http://www.excelforum.com/showthread.php?threadid=384449
>
>

Excel Programming >> Matching
Thanks we will give it a go

--
Skyhigh
------------------------------------------------------------------------
Skyhigh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24907
View this thread: http://www.excelforum.com/showthread.php?threadid=384449