Data sorting Questions...

[JJEvil]

Hey Excel Ninjas... I have been a silent worshipper of this site for a while but now I have to ask something...

I have an issue with a lot of text that can only be dumped by the sytem into one column. The only problem is that it needs to be parsed and sorted by a bunch of different things. I have searched all over and have made "some" progress but I am at a loss how make it work.
The text pastes like this - all in ONE column:



From: 08/01/09 IEX TotalView Detail and Summary
To: 08/08/09 IEX07-C1 Date Range
Shift: 0 All Day Time Utilization Report Page: 1

MU: xxxxxxxxxxxxxxxxSorted by: Id
Time Zone: Canada/Eastern
Report Across Agent Moves: Yes Report Agent Moves: No
Report Exceptions:: Meeting Any Condition
Include Codes:
After Call Work (Greater Than 0:00)
Break Offline (Greater Than 0:00)
Coaching 1:1 (Greater Than 0:00)
Coaching 1:1 Offline (Greater Than 0:00)
Late-Leave Early (Greater Than 0:00)
MIA (Greater Than 0:00)
Miss (Greater Than 0:00)
Multimedia (Greater Than 0:00)
Nesting Training (Greater Than 0:00)
No Call/No Show (Greater Than 0:00)
Off Phone Misc (Greater Than 0:00)
Offline (Greater Than 0:00)
Open Time (Greater Than 0:00)
Other Prod Offline (Greater Than 0:00)
Other Prod Time (Greater Than 0:00)
Pre-shift (Greater Than 0:00)
Pre-shift Offline (Greater Than 0:00)
Shift Trade-Working (Greater Than 0:00)
Team Lead (Greater Than 0:00)
Team Lead Offline (Greater Than 0:00)
Team Meeting (Greater Than 0:00)
Training (Greater Than 0:00)
Training Offline (Greater Than 0:00)
UnApproved Absence (Greater Than Duration
Exception Code HH:MM Percent
Duration
Exception Code HH:MM Percent
08/01/09 - 08/08/09
42 Name, Agent
Break Offline 0:45 3.00%
Open Time 15:45 63.00%
UnApproved Absence 8:30 34.00%
Total 25:00

150 Name, Agent
Open Time 13:37 100.00%
Total 13:37

196 Name, Agent
Open Time 3:30 100.00%
Total 3:30

____________________

From: 08/01/09 IEX TotalView Detail and Summary
To: 08/08/09 IEX07-C1 Date Range
Shift: 0 All Day Time Utilization Report Page: 2

MU: xxxxxxxxxxxxxxxxxxxx Sorted by: Id
Time Zone: Canada/Eastern
Report Across Agent Moves: Yes Report Agent Moves: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Exception Codes: No
Show Exception Code Details: No
Duration
Exception Code HH:MM Percent
205 Name, Agent
Open Time 4:30 100.00%
Total 4:30

214 Name, Agent
Open Time 3:40 100.00%
Total 3:40

220 Name, Agent
Break Offline 0:30 2.76%
Open Time 17:35 97.24%
Total 18:05

255 Name, Agent
Open Time 3:30 100.00%
Total 3:30

342 Name, Agent
Break Offline 0:15 1.47%
Late-Leave Early 0:09 0.88%
Open Time 16:36 97.65%
Total 17:00

395 Name, Agent
Break Offline 0:30 4.05%
Open Time 11:50 95.95%
Total 12:20

470 Name, Agent
MIA 3:30 100.00%
Total 3:30

472 Name, Agent
Open Time 1:00 100.00%
Total 1:00

503 Name, Agent
Open Time 3:30 100.00%
Total 3:30

____________________

From: 08/01/09 IEX TotalView Detail and Summary
To: 08/08/09 IEX07-C1 Date Range
Shift: 0 All Day Time Utilization Report Page: 3

MU: xxxxxxxxxxxxxxxxxxxxxx Sorted by: Id
Time Zone: Canada/Eastern
Report Across Agent Moves: Yes Report Agent Moves: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Exception Codes: No
Show Exception Code Details: No
Duration
Exception Code HH:MM Percent
510 Name, Agent
Open Time 3:30 100.00%
Total 3:30

511 Name, Agent
Open Time 3:30 100.00%
Total 3:30
521 Name, Agent
Late-Leave Early 3:30 100.00%
Total 3:30

577 Name, Agent
Open Time 3:30 100.00%
Total 3:30

600 Name, Agent
Open Time 3:30 100.00%
Total 3:30

899 Name, Agent
Break Offline 0:15 1.54%
Coaching 1:1 Offline 0:45 4.62%
Late-Leave Early 0:06 0.62%
Open Time 15:07 93.22%
Total 16:13

906 Name, Agent
Break Offline 2:30 6.25%
Late-Leave Early 0:30 1.25%
Nesting Training 37:00 92.50%
Total 40:00

920 Name, Agent
Break Offline 2:30 5.29%
Open Time 35:16 74.61%
Training Offline 1:30 3.17%
UnApproved Absence 8:00 16.93%
Total 47:16

____________________


What I need to do is find a way to extract the agents ID (the number next to their name) into one column then have the next columns be the % of thier various activites. The issue that I am hitting again and again is that each agent might have completely different activities. I eventually want a format that I can dump into a pivot and look at a whole department by manager (easy to look up later). As you can see there is a lot of garbage - all i need is the agent's ID and their activity breakdown accross a row. The rest is just noise. Oh, the list can go up to about 500 agents.

Help!

 

[JJEvil]

Excel Workbook
	A
1
2
3	From: 08/01/09 IEX TotalView Detail and Summary
4	To: 08/27/09 IEX07-C1 Date Range
5	Shift: 0 All Day Time Utilization Report Page: 1
6
7
8	MU: xxxxxxxxxxxxxxxx Sorted by: Name
9	Time Zone: Canada/Eastern
10	Report Across Agent Moves: No Report Agent Moves: No
11	Report Exceptions:: Meeting Any Condition
12	Include Codes:
13	After Call Work (Greater Than 0:00)
14	Agent No Seat (Greater Than 0:00)
15	Aux Time (Greater Than 0:00)
16	Billable Train (Greater Than 0:00)
17	Break Offline (Greater Than 0:00)
18	Callbacks (Greater Than 0:00)
19	Clerical (Greater Than 0:00)
20	Clerical Offline (Greater Than 0:00)
21	Coaching 1:1 (Greater Than 0:00)
22	Coaching 1:1 Offline (Greater Than 0:00)
23	DNU-Family Day (Greater Than 0:00)
24	DNU-India Transport (Greater Than 0:00)
25	DNU-Overtime (Greater Than 0:00)
26	DNU-Personal Day Can (Greater Than 0:00)
27	DNU-Req Day Off (Greater Than 0:00)
28	DNU-Research (Greater Than 0:00)
29	DNU-Staff-Supervisor (Greater Than 0:00)
30	DNU-Sup Hotline (Greater Than 0:00)
dump

Excel Workbook
	A
31	DNU-Team Meeting Off (Greater Than 0:00)
32	DNU-UTO Partial Day (Greater Than 0:00)
33	Extra Hours (Greater Than 0:00)
34	Flex Down (Greater Than 0:00)
35	Flex Up (Greater Than 0:00)
36	Holid Duration
37	Exception Code HH:MM Percent
38	Duration
39	Exception Code HH:MM Percent
40
41	08/01/09 - 08/27/09
42
43	15174 Last name, First name
44	Aux Time 2:40 1.32%
45	Break Offline 10:30 5.21%
46	Coaching 1:1 Offline 3:15 1.61%
47	Extra Hours 1:05 0.54%
48	Know 4:01 1.99%
49	Late-Leave Early 8:40 4.30%
50	MIA 0:34 0.28%
51	Miss 0:19 0.16%
52	Open Time 155:23 77.16%
53	Shift Trade-Working 4:40 2.32%
54	Team Meeting 1:05 0.54%
55	Training Offline 9:10 4.55%
56	Total 201:22
57
58	____________________
59
60
Excel 2002 dump

Excel Workbook
	A
61	From: 08/01/09 IEX TotalView Detail and Summary
62	To: 08/27/09 IEX07-C1 Date Range
63	Shift: 0 All Day Time Utilization Report Page: 2
64
65
66	MU: xxxxxxxxxxxxxxxxxxx Sorted by: Name
67	Time Zone: Canada/Eastern
68	Report Across Agent Moves: No Report Agent Moves: No
69	Sort By Exception Code Group: No
70	Show Exception Code Group Totals For Selected Exception Codes: No
71	Show Exception Code Details: No
72
73	Duration
74	Exception Code HH:MM Percent
75
76	28108 Last name, First name
77	Aux Time 1:20 1.31%
78	Break Offline 4:15 4.19%
79	Late-Leave Early 0:14 0.23%
80	Miss 0:15 0.25%
81	No Call/No Show 8:00 7.88%
82	Open Time 62:51 61.92%
83	Shift Trade-Working 5:00 4.93%
84	Training Offline 3:35 3.53%
85	UnApproved Absence 16:00 15.76%
86	Total 101:30
87
88	34630 Last name, First name
89	Aux Time 2:05 1.53%
90	Break Offline 6:30 4.76%
Excel 2002 dump

Excel Workbook
	A
91	Coaching 1:1 Offline 1:30 1.10%
92	Know 0:15 0.18%
93	Late-Leave Early 2:33 1.87%
94	MIA 2:17 1.67%
95	Miss 0:15 0.18%
96	Open Time 84:15 61.72%
97	Training Offline 4:50 3.54%
98	UnApproved Absence 32:00 23.44%
99	Total 136:30
100
101	32680 Last name, First name
102	Aux Time 2:20 1.59%
103	Break Offline 6:30 4.43%
104	Extra Hours 2:00 1.36%
105	Know 1:00 0.68%
106	Late-Leave Early 7:21 5.01%
107	MIA 9:48 6.69%
108	No Call/No Show 8:00 5.46%
109	Open Time 71:28 48.76%
110	Team Meeting 1:05 0.74%
111	Training Offline 7:05 4.83%
112	UnApproved Absence 29:57 20.43%
113	Total 146:34
114
115
116	____________________
117
118
119	From: 08/01/09 IEX TotalView Detail and Summary
120	To: 08/27/09 IEX07-C1 Date Range
Excel 2002 dump

Excel 2002

 

[xenou]

Here's an idea. Improvements are most welcome. I separated the times also since it was readily available in the process - and used both the total time and the total percent as check figures to catch any errors. There is an outer loop for the names, and an inner loop for the jobs that goes with each name. I'm assuming we have very consistent output to work with - but always use test cases for anything unusual that might occur (for instance, a name with 0 hours worked, if that could happen).

My code assumes you delete the stuff at the top and/or bottom - only the names with their jobs should remain, starting at row 1.

<a href="http://northernocean.net/etc/mrexcel/20090903_jobs.zip">Sample Workbook</a>

Sub JobsAndPercentsByName()
[COLOR="SeaGreen"]'//Sub will strip out from a single column:
'1) ID and Name
'2) Job
'3) Time
'4) Percent of Time[/COLOR]

[COLOR="#2e8b57"]'//ASSUMPTIONS:
    'Column A and only column A is populated
    'Row 1 starts with the first name -
        'i.e., other information above or below the
        'names are deleted from the worksheet
    'There is a blank Row between the names[/COLOR]


Dim i As Long
Dim LRow As Long
Dim ws As Worksheet

Dim arg As String
Dim strName As String
Dim strPercent As String
Dim strTime As String
Dim strJob As String

Dim dblTotalTime As Double
Dim dblTotalPercent As Double


Set ws = ActiveSheet
ws.Range("B1:G1").EntireColumn.ClearContents
ws.Range("B1:G1").EntireColumn.Font.ColorIndex = 0
ws.Range("B1:G1").EntireColumn.Font.Bold = False

With ws

    i = 1
    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    Do While i <= LRow
        
        [COLOR="#2e8b57"]'//Code and Name[/COLOR]
        strName = .Cells(i, 1).Value
        dblTotalTime = 0
        dblTotalPercent = 0
            
        Do While Len(.Cells(i + 1, 1).Value) > 0
            i = i + 1
            arg = .Cells(i, 1).Value
            
            [COLOR="#2e8b57"]'//Check for key characters used in pattern matching[/COLOR]
            If InStr(1, arg, "%", vbBinaryCompare) > 0 Then
                If InStr(1, arg, " ", vbBinaryCompare) > 0 Then
                    If InStr(1, arg, ":", vbBinaryCompare) > 0 Then
                        
                        [COLOR="#2e8b57"]'//Strip out Job, Time, Percent
                        '//strategy is to work from the right, removing each piece of information as we go[/COLOR]
                        strPercent = GetPercent(arg)
                        arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
                        strTime = GetTime(arg)
                        arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
                        strJob = arg
                    
                        [COLOR="#2e8b57"]'//Write to sheet[/COLOR]
                        .Cells(i, 4).Value = strName
                        .Cells(i, 5).Value = strJob
                        .Cells(i, 6).Value = strTime
                        .Cells(i, 7).Value = strPercent
                        
                        [COLOR="#2e8b57"]'//Check figures[/COLOR]
                        dblTotalTime = dblTotalTime + .Cells(i, 6).Value
                        dblTotalPercent = dblTotalPercent + .Cells(i, 7).Value
                    
                    End If
                End If
            
            [COLOR="#2e8b57"]'//Total Hours[/COLOR]
            ElseIf Left(arg, 5) = "Total" Then
                
                .Cells(i, 4).Value = strName
                .Cells(i, 6).Value = Trim(Replace(arg, "Total", ""))
                
                [COLOR="#2e8b57"]'//Check figures[/COLOR]
                If Round(.Cells(i, 6).Value, 6) <> Round(dblTotalTime, 6) Then
                    .Cells(i, 6).Font.ColorIndex = 3
                    .Cells(i, 6).Font.Bold = True
                End If
                If Round(Abs(1 - dblTotalPercent), 4) > 0.0001 Then
                    .Cells(i, 7).Font.ColorIndex = 3
                    .Cells(i, 7).Font.Bold = True
                    .Cells(i, 7).Value = "Error"
                End If
                
            End If
                        
        Loop
        
        [COLOR="#2e8b57"]'//Skip blank row[/COLOR]
        i = i + 2
    
    Loop
    
End With

End Sub
'----------------------------------------------
Function GetTime(ByVal arg As String) As String
GetTime = Right(arg, InStr(1, StrReverse(arg), " ", vbBinaryCompare) - 1)
End Function
'----------------------------------------------
Function GetPercent(ByVal arg As String) As String
GetPercent = Right(arg, InStr(1, StrReverse(arg), Chr(32), vbBinaryCompare) - 1)
End Function

 

[JJEvil]

That worked amazingly! Thanks you very much!

 

[mgorman]

Public Sub TVR()
'variables for the files
Dim FileName As String
Dim fso As New FileSystemObject
Dim fls As Files
Dim f As File
Dim txtStream As TextStream
'Program variables
Dim strCur As String, TempstrCur As String, EndTime As String, MU As String, TempMU As String
'opens the folder where the TVR files are
FileName = Sheets("Control").Range("G11").Value
Set fls = fso.GetFolder(FileName).Files
Application.ScreenUpdating = False
Dim count As Integer, a As Integer
count = 2
For Each f In fls

Set txtStream = fspenTextFile(FileName & "\" & f.Name, ForReading)

Do While Not txtStream.AtEndOfStream
strCur = txtStream.ReadLine
TempstrCur = Left(strCur, 41)
EndTime = Left(strCur, 47)
EndTime = Right(EndTime, 5)
If (Left(strCur, 53) = "\par Daily Start/Stop") Then
MU = Left(strCur, 85)
MU = Right(MU, 28)
If MU = " Austin Saturn/Saab" Then
TempMU = Left(MU, 23)
MU = Right(MU, 4)
MU = TempMU + MU
End If
End If
For a = 0 To 9
If (Left(strCur, 11) = "\par " + CStr(a)) Then
TempstrCur = Right(TempstrCur, 31)
Sheets("Schedules").Range("A" & count).Value = Left(TempstrCur, 4)
Sheets("Schedules").Range("D" & count).Value = Right(TempstrCur, 5)
TempstrCur = Right(TempstrCur, 26)
Sheets("Schedules").Range("C" & count).Value = Left(TempstrCur, 21)
Sheets("Schedules").Range("F" & count).Value = EndTime
Sheets("Schedules").Range("G" & count).Value = MU
count = count + 1
End If
Next
Loop
Set txtStream = Nothing
Next f
Set fls = Nothing

Application.ScreenUpdating = True
Worksheets("Schedules").Columns.AutoFit
End Sub

Just read the file in, and look for certain words, to seperate the file. This function reades the schedules from IEX, same concept for all reports. To get a real look at what the report will read into excel, open the TVR with notebook.

 

[paconovellino]

Here's an idea. Improvements are most welcome. I separated the times also since it was readily available in the process - and used both the total time and the total percent as check figures to catch any errors. There is an outer loop for the names, and an inner loop for the jobs that goes with each name. I'm assuming we have very consistent output to work with - but always use test cases for anything unusual that might occur (for instance, a name with 0 hours worked, if that could happen).

My code assumes you delete the stuff at the top and/or bottom - only the names with their jobs should remain, starting at row 1.

Sample Workbook

Sub JobsAndPercentsByName()
[COLOR=SeaGreen]'//Sub will strip out from a single column:
'1) ID and Name
'2) Job
'3) Time
'4) Percent of Time[/COLOR]

[COLOR=#2e8b57]'//ASSUMPTIONS:
    'Column A and only column A is populated
    'Row 1 starts with the first name -
        'i.e., other information above or below the
        'names are deleted from the worksheet
    'There is a blank Row between the names[/COLOR]


Dim i As Long
Dim LRow As Long
Dim ws As Worksheet

Dim arg As String
Dim strName As String
Dim strPercent As String
Dim strTime As String
Dim strJob As String

Dim dblTotalTime As Double
Dim dblTotalPercent As Double


Set ws = ActiveSheet
ws.Range("B1:G1").EntireColumn.ClearContents
ws.Range("B1:G1").EntireColumn.Font.ColorIndex = 0
ws.Range("B1:G1").EntireColumn.Font.Bold = False

With ws

    i = 1
    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    Do While i <= LRow
        
        [COLOR=#2e8b57]'//Code and Name[/COLOR]
        strName = .Cells(i, 1).Value
        dblTotalTime = 0
        dblTotalPercent = 0
            
        Do While Len(.Cells(i + 1, 1).Value) > 0
            i = i + 1
            arg = .Cells(i, 1).Value
            
            [COLOR=#2e8b57]'//Check for key characters used in pattern matching[/COLOR]
            If InStr(1, arg, "%", vbBinaryCompare) > 0 Then
                If InStr(1, arg, " ", vbBinaryCompare) > 0 Then
                    If InStr(1, arg, ":", vbBinaryCompare) > 0 Then
                        
                        [COLOR=#2e8b57]'//Strip out Job, Time, Percent
                        '//strategy is to work from the right, removing each piece of information as we go[/COLOR]
                        strPercent = GetPercent(arg)
                        arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
                        strTime = GetTime(arg)
                        arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
                        strJob = arg
                    
                        [COLOR=#2e8b57]'//Write to sheet[/COLOR]
                        .Cells(i, 4).Value = strName
                        .Cells(i, 5).Value = strJob
                        .Cells(i, 6).Value = strTime
                        .Cells(i, 7).Value = strPercent
                        
                        [COLOR=#2e8b57]'//Check figures[/COLOR]
                        dblTotalTime = dblTotalTime + .Cells(i, 6).Value
                        dblTotalPercent = dblTotalPercent + .Cells(i, 7).Value
                    
                    End If
                End If
            
            [COLOR=#2e8b57]'//Total Hours[/COLOR]
            ElseIf Left(arg, 5) = "Total" Then
                
                .Cells(i, 4).Value = strName
                .Cells(i, 6).Value = Trim(Replace(arg, "Total", ""))
                
                [COLOR=#2e8b57]'//Check figures[/COLOR]
                If Round(.Cells(i, 6).Value, 6) <> Round(dblTotalTime, 6) Then
                    .Cells(i, 6).Font.ColorIndex = 3
                    .Cells(i, 6).Font.Bold = True
                End If
                If Round(Abs(1 - dblTotalPercent), 4) > 0.0001 Then
                    .Cells(i, 7).Font.ColorIndex = 3
                    .Cells(i, 7).Font.Bold = True
                    .Cells(i, 7).Value = "Error"
                End If
                
            End If
                        
        Loop
        
        [COLOR=#2e8b57]'//Skip blank row[/COLOR]
        i = i + 2
    
    Loop
    
End With

End Sub
'----------------------------------------------
Function GetTime(ByVal arg As String) As String
GetTime = Right(arg, InStr(1, StrReverse(arg), " ", vbBinaryCompare) - 1)
End Function
'----------------------------------------------
Function GetPercent(ByVal arg As String) As String
GetPercent = Right(arg, InStr(1, StrReverse(arg), Chr(32), vbBinaryCompare) - 1)
End Function

Amazing!!!

Is there a way you can modify the macro to keep the date??