http://www.neilcarden.co.uk/example.xls

Please see above file... I have a sheet (import) which is tha raw report from an application, I want to sort it in a similar way to LOOKUPs where it looks for the agents name, and returns the data as in sheet 2 (Formatted).

How easy is this to do? as some days not all the codes will be used so the raw data will not always be in the same format regarding number of rows before going onto next person etc...

Thanks in advance
Neil.

Neil

This is a general forum you should enter the data here so every one can see
the problem. However, I downloaded your file and have written a macro to
solve your problem.

Option Base 1
Sub FormatData()
Dim x, c, r As Long, nr As Long
Dim col As Integer, ncol As Integer
Dim rng As Range
Worksheets("imported").Select
Application.ScreenUpdating = True
' set up the headers to look up
x = Array("Break", "Office", "Duty Senior", "Meeting", _
"Development", "Read Time", "CPVA", "Not Ready")
nr = Range("A2").CurrentRegion.Rows.Count
r = 3
Set rng = Range(Cells(2, 1), Cells(nr, 1))

Worksheets("formatted").Select
ActiveSheet.Cells.Clear
Range("B3:I3") = x
On Error Resume Next
' find each employee by works#
For Each c In rng
If IsNumeric(Right(c, 4)) * 1 Then
r = r + 1
Cells(r, 1) = c
End If

' check column A against headers X
For i = 1 To 8
If x(i) = c Then
Cells(r, i + 1) = c.Offset(0, 1)
End If
Next i
Next c
Range("A3:I3").Select
Selection.Font.Bold = True
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A3").Select

Application.ScreenUpdating = True
End Sub

any problems contact me


"neilcarden" wrote:


http://www.neilcarden.co.uk/example.xls

Please see above file... I have a sheet (import) which is tha raw
report from an application, I want to sort it in a similar way to
LOOKUPs where it looks for the agents name, and returns the data as in
sheet 2 (Formatted).

How easy is this to do? as some days not all the codes will be used so
the raw data will not always be in the same format regarding number of
rows before going onto next person etc...

Thanks in advance
Neil.




--
neilcarden