Data Sorting Macro

[paconovellino]

I have a problem with a lot of text that can only be imported to excel into one column (A). The only problem is that it needs to be parsed and sorted by several different things. I have found it difficult to find any information on how to modify the information.

This is how the text looks like in Column A:

From: 09/01/13 IEX TotalView Detail and Summary
To: 09/26/13 xxxxxxxx xxxxxxxx S.A. de C.V. Daily
Shift: 0 All Day Time Utilization Report Page: 4
MU: 2106 RIM BCTS Sorted by: Name
Time Zone: America/Costa_Rica
Report Across Agent Moves: No 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
ID Agent Name Date: 09/04/13
1536 Avalos, Rodolfo
Client Training 10:00 100.00%
Total 10:00
1713 Piedra, Juan
Client Training 10:00 100.00%
Total 10:00
Summary Data For: Date: 09/04/13
Total Agents: 2
Client Training 20:00 100.00%
Total 20:00
____________________
From: 09/01/13 IEX TotalView Detail and Summary
To: 09/26/13 xxxxxxxx xxxxxxxx S.A. de C.V. Daily
Shift: 0 All Day Time Utilization Report Page: 5
MU: 2106 RIM BCTS Sorted by: Name
Time Zone: America/Costa_Rica
Report Across Agent Moves: No 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
ID Agent Name Date: 09/05/13
1536 Avalos, Rodolfo
Client Training 10:00 100.00%
Total 10:00
2429 Núñez, Andres
Client Training 5:15 100.00%
Total 5:15
1713 Piedra, Juan
Client Training 10:00 100.00%
Total 10:00
1874 Urena, Carlo
Client Training 9:00 100.00%
Total 9:00
Summary Data For: Date: 09/05/13
Total Agents: 4
Client Training 34:15 100.00%
Total 34:15
____________________
From: 09/01/13 IEX TotalView Detail and Summary
To: 09/26/13 xxxxxxxx xxxxxxxx S.A. de C.V. Daily
Shift: 0 All Day Time Utilization Report Page: 6
MU: 2106 RIM BCTS Sorted by: Name
Time Zone: America/Costa_Rica
Report Across Agent Moves: No 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
ID Agent Name Date: 09/06/13
1713 Piedra, Juan
Client Training 10:00 100.00%
Total 10:00
Summary Data For: Date: 09/06/13
Total Agents: 1
Client Training 10:00 100.00%
Total 10:00
____________________
What I need the macro to do is find a way to extract the date, the agents ids (number next to name) in one column, the agent's name in one column, the activity (client training)in one column and the total (10:00) in other column. What is breaking my head is that each agent might have completely different activities and that the list can be as long as 1000 agents

You can view the excel file attached to have a better idea

https://www.dropbox.com/s/9mxs1wonu53umm1/Macro Data.xlsx

Thanks in advance for the help

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paconovellino]

So far I got this macro that I found on the internet, but I cant get it to get the date

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

'//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


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

'//Code and Name
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

'//Check for key characters used in pattern matching
If InStr(1, arg, "%", vbBinaryCompare) > 0 Then
If InStr(1, arg, " ", vbBinaryCompare) > 0 Then
If InStr(1, arg, ":", vbBinaryCompare) > 0 Then

'//Strip out Job, Time, Percent
'//strategy is to work from the right, removing each piece of information as we go
strPercent = GetPercent(arg)
arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
strTime = GetTime(arg)
arg = Trim(Replace(arg, strPercent, "", 1, -1, vbBinaryCompare))
strJob = arg

'//Write to sheet
.Cells(i, 4).Value = strName
.Cells(i, 5).Value = strJob
.Cells(i, 6).Value = strTime
.Cells(i, 7).Value = strPercent

'//Check figures
dblTotalTime = dblTotalTime + .Cells(i, 6).Value
dblTotalPercent = dblTotalPercent + .Cells(i, 7).Value

End If
End If

'//Total Hours
ElseIf Left(arg, 5) = "Total" Then

.Cells(i, 4).Value = strName
.Cells(i, 6).Value = Trim(Replace(arg, "Total", ""))

'//Check figures
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

'//Skip blank row
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

 

[PHV]

Anyway, this is unclear what is the expected result with your posted sample input.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paconovellino]

I want to extract the date, the agents ids (number next to name), the agent's name, the activity (client training)and the total (10:00) on different columns and repeat it until all data is formatted.

Thanks

 

[PHV]

Again, please post the expected result.

 

[paconovellino]

The result should look something like this


Date: 09/08/13 1536 Avalos, Rodolfo Client Training 10:00

Date column B, Id column C, Agent Name Column D, activity Column E and Total time in activity Column F

Thanks

 

[Andrzejek]

Are you saying from this block of text:

From: 09/01/13 IEX TotalView Detail and Summary
To: 09/26/13 xxxxxxxx xxxxxxxx S.A. de C.V. Daily
Shift: 0 All Day Time Utilization Report Page: 4
MU: 2106 RIM BCTS Sorted by: Name
Time Zone: America/Costa_Rica
Report Across Agent Moves: No 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
ID Agent Name [blue]Date: 09/04/13
1536 Avalos, Rodolfo
Client Training 10:00[/blue] 100.00%
Total 10:00
1713 Piedra, Juan
Client Training 10:00 100.00%
Total 10:00
Summary Data For: Date: 09/04/13
Total Agents: 2
Client Training 20:00 100.00%
Total 20:00

You want to get out:[pre]
A |B |C |D |E |F
|Date: 09/08/13 |1536 |Avalos, Rodolfo |Client Training |10:00
[/pre]

Ia that correct?

Have fun.

---- Andy

 

[mscallisto]

Maybe:

|Date: 09/0[highlight #C17D11]4[/highlight]/13

 

[SkipVought]

and maybe this...
[tt]
A |B |C |D |E |F
|Date: 09/08/13 |1536 |Avalos, Rodolfo |Client Training |10:00
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oops...

Maybe this???
[pre]
A |B |C |D |E |F
|Date: 09/08/13 |1536 |Avalos, Rodolfo |Client Training |10:00
|Date: 09/08/13 |1713 |Piedra, Juan |Client Training |10:00
[/pre]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

We have to wait for paconovellino’s response since many of us here at TT have company polices against downloading/viewing/opening any files from the Internet.

So far those are our best guesses of what the outcome should be…


Have fun.

---- Andy

 

[paconovellino]

Hi, and thank you so much for your replys.

I need the information to be displayed as SkipVought mention on his second replay.

 

[Andrzejek]

This is what I've got as an outcome:
[pre]
Date: 09/04/13 1536 Avalos, Rodolfo Client Training 10:00
Date: 09/04/13 1713 Piedra, Juan Client Training 10:00
Date: 09/05/13 1536 Avalos, Rodolfo Client Training 10:00
Date: 09/05/13 2429 Núñez, Andres Client Training 5:15
Date: 09/05/13 1713 Piedra, Juan Client Training 10:00
Date: 09/05/13 1874 Urena, Carlo Client Training 9:00
Date: 09/06/13 1713 Piedra, Juan Client Training 10:00
[/pre]

Using some assumptions:
1. All data from your original post is in a text file ("C:\TEMP\test.txt")
2. Data starts with the line: "[tt]ID Agent Name Date:[/tt]"
3. Data stops with the line: "[tt]Summary Data For:[/tt]"
4. 'activity' is always 2 words (like "[tt]Client Training[/tt]")

Using this code in Excel:

Dim aryTemp() As String
Dim strInputData As String
Dim aryPieces() As String
Dim i As Integer
Dim blnCollectData As Boolean

Dim strMyDate As String
Dim strMyID As String
Dim strMyGuy As String
Dim strMyWork As String
Dim strMyNo As String

Open "C:\TEMP\test.txt" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1
aryTemp = Split(strInputData, vbCrLf)

For i = LBound(aryTemp) To UBound(aryTemp)

    If InStr(aryTemp(i), "ID Agent Name Date:") Then
        aryPieces = Split(aryTemp(i), " ")
        strMyDate = aryPieces(3) & " " & aryPieces(4)
        blnCollectData = True
        i = i + 1
    ElseIf InStr(aryTemp(i), "Summary Data For:") Then
        blnCollectData = False
    End If
    
    If blnCollectData Then
        aryPieces = Split(aryTemp(i), " ")
        If IsNumeric(aryPieces(0)) Then
            strMyID = aryPieces(0)
            strMyGuy = aryPieces(1) & " " & aryPieces(2)
        Else
            If InStr(aryPieces(0), "Total") = 0 Then
                strMyWork = aryPieces(0) & " " & aryPieces(1)
                strMyNo = aryPieces(2)
            End If
        End If
    End If
    
    If Len(strMyNo) > 0 Then
        Debug.Print strMyDate & " " & strMyID & " " & strMyGuy & " " & strMyWork & " " & strMyNo
        strMyID = ""
        strMyGuy = ""
        strMyWork = ""
        strMyNo = ""
    End If
Next i

You can use the [tt]Debug.Print[/tt] line to insert info into cells in Excel.


Have fun.

---- Andy

 

[paconovellino]

thanks Andy!