Sorting data into different format....

N

neilcarden

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

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

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

Thanks in advance
Neil
 
P

PeterAtherton

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 [email protected]
 
Top