Data sorting Questions...

J

JJEvil

New Member
Joined
Sep 1, 2009
Messages
3
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! :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Excel Workbook
A
1
2
3From: 08/01/09 IEX TotalView Detail and Summary
4To: 08/27/09 IEX07-C1 Date Range
5Shift: 0 All Day Time Utilization Report Page: 1
6
7
8MU: xxxxxxxxxxxxxxxx Sorted by: Name
9Time Zone: Canada/Eastern
10Report Across Agent Moves: No Report Agent Moves: No
11Report Exceptions:: Meeting Any Condition
12Include Codes:
13After Call Work (Greater Than 0:00)
14Agent No Seat (Greater Than 0:00)
15Aux Time (Greater Than 0:00)
16Billable Train (Greater Than 0:00)
17Break Offline (Greater Than 0:00)
18Callbacks (Greater Than 0:00)
19Clerical (Greater Than 0:00)
20Clerical Offline (Greater Than 0:00)
21Coaching 1:1 (Greater Than 0:00)
22Coaching 1:1 Offline (Greater Than 0:00)
23DNU-Family Day (Greater Than 0:00)
24DNU-India Transport (Greater Than 0:00)
25DNU-Overtime (Greater Than 0:00)
26DNU-Personal Day Can (Greater Than 0:00)
27DNU-Req Day Off (Greater Than 0:00)
28DNU-Research (Greater Than 0:00)
29DNU-Staff-Supervisor (Greater Than 0:00)
30DNU-Sup Hotline (Greater Than 0:00)
dump
Excel Workbook
A
31DNU-Team Meeting Off (Greater Than 0:00)
32DNU-UTO Partial Day (Greater Than 0:00)
33Extra Hours (Greater Than 0:00)
34Flex Down (Greater Than 0:00)
35Flex Up (Greater Than 0:00)
36Holid Duration
37Exception Code HH:MM Percent
38Duration
39Exception Code HH:MM Percent
40
4108/01/09 - 08/27/09
42
4315174 Last name, First name
44Aux Time 2:40 1.32%
45Break Offline 10:30 5.21%
46Coaching 1:1 Offline 3:15 1.61%
47Extra Hours 1:05 0.54%
48Know 4:01 1.99%
49Late-Leave Early 8:40 4.30%
50MIA 0:34 0.28%
51Miss 0:19 0.16%
52Open Time 155:23 77.16%
53Shift Trade-Working 4:40 2.32%
54Team Meeting 1:05 0.54%
55Training Offline 9:10 4.55%
56Total 201:22
57
58____________________
59
60
Excel 2002 dump
Excel Workbook
A
61From: 08/01/09 IEX TotalView Detail and Summary
62To: 08/27/09 IEX07-C1 Date Range
63Shift: 0 All Day Time Utilization Report Page: 2
64
65
66MU: xxxxxxxxxxxxxxxxxxx Sorted by: Name
67Time Zone: Canada/Eastern
68Report Across Agent Moves: No Report Agent Moves: No
69Sort By Exception Code Group: No
70Show Exception Code Group Totals For Selected Exception Codes: No
71Show Exception Code Details: No
72
73Duration
74Exception Code HH:MM Percent
75
7628108 Last name, First name
77Aux Time 1:20 1.31%
78Break Offline 4:15 4.19%
79Late-Leave Early 0:14 0.23%
80Miss 0:15 0.25%
81No Call/No Show 8:00 7.88%
82Open Time 62:51 61.92%
83Shift Trade-Working 5:00 4.93%
84Training Offline 3:35 3.53%
85UnApproved Absence 16:00 15.76%
86Total 101:30
87
8834630 Last name, First name
89Aux Time 2:05 1.53%
90Break Offline 6:30 4.76%
Excel 2002 dump
Excel Workbook
A
91Coaching 1:1 Offline 1:30 1.10%
92Know 0:15 0.18%
93Late-Leave Early 2:33 1.87%
94MIA 2:17 1.67%
95Miss 0:15 0.18%
96Open Time 84:15 61.72%
97Training Offline 4:50 3.54%
98UnApproved Absence 32:00 23.44%
99Total 136:30
100
10132680 Last name, First name
102Aux Time 2:20 1.59%
103Break Offline 6:30 4.43%
104Extra Hours 2:00 1.36%
105Know 1:00 0.68%
106Late-Leave Early 7:21 5.01%
107MIA 9:48 6.69%
108No Call/No Show 8:00 5.46%
109Open Time 71:28 48.76%
110Team Meeting 1:05 0.74%
111Training Offline 7:05 4.83%
112UnApproved Absence 29:57 20.43%
113Total 146:34
114
115
116____________________
117
118
119From: 08/01/09 IEX TotalView Detail and Summary
120To: 08/27/09 IEX07-C1 Date Range
Excel 2002 dump
Excel 2002
 
Upvote 0
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>

Code: 
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
 
Last edited:
Upvote 0
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 = fso_OpenTextFile(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.
 
Upvote 0
xenou said:
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

Code: 
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
Click to expand...


Amazing!!!

Is there a way you can modify the macro to keep the date??
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
350
Lukma
L
R
  • Solved
Generated report has an invalid format. Unable to action anything with cell value.
Replies
3
Views
329
MARK858
MARK858
L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
972
Armstrong_N14
A
Z
  • Solved
Calculate hours and minutes between two dates and time showing Early, Late, On Time
Replies
8
Views
967
Fluff
Fluff

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top