Organizing data in Excel using VBA 1

Wray69 · Apr 19, 2006

Been a long time since I have been here and I still have not improved in VBA.

Anyway I have some data that is not formated Excel friendly. It looks like below;

From: 04/19/06 IEX TotalV iew Detail and Summary
To: 04/19/06 Sprint C MG Daily
Shift: 0 All Day Time Utilizatio n Report Page: 2

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
Report Across Agent Moves: No Re port Agent Move s: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Ex ception Codes: No
Show Exception Code Details: No

Duration
Exception Code HH:MM Percent
32635 BARSKY CRISTY
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

17953 BASTIEN AMANDA
Break R99 0:30 7.23%
Open Time R99 6:25 92.77%
Total 6:55

2876 BAUMGARTEN TIFFANY
Break R99 0:40 9.52%
Open Time R99 6:20 90.48%
Total 7:00

18179 BEHL KERRI
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

7394 BERRY BRENDA
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36632 BEST STANLEY
Open Time R99 6:48 100.00%
Total 6:48

32594 BLANCHETTE FRANKIE
Break R99 0:30 6.74%
Open Time R99 6:55 93.26%
Total 7:25

18333 BOROUGHF ALLAN
OT - Online X01 2:24 100.00%
Total 2:24

35679 BRADFORD LAKISHA
Open Time R99 1:30 100.00%

____________________

From: 04/19/06 IEX TotalV iew Detail and Summary
To: 04/19/06 Sprint C MG Daily
Shift: 0 All Day Time Utilizatio n Report Page: 3

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
Report Across Agent Moves: No Re port Agent Move s: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Ex ception Codes: No
Show Exception Code Details: No

Duration
Exception Code HH:MM Percent
Total 1:30

32972 BROWN DOUG E
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

30929 BROWN MARSHALL
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

7395 BRYAN PERRY
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

36670 BRYANT DIANNA
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

3272 BURGE TAMICHA
Open Time R99 6:30 100.00%
Total 6:30

36456 BURTON LISA I
Break R99 0:30 8.93%
Open Time R99 5:06 91.07%
Total 5:36

1060 CALDWELL INDIA
OT - Online X01 0:25 100.00%
Total 0:25

11680 CANNON HEATHER
Open Time R99 6:30 100.00%
Total 6:30

____________________

From: 04/19/06 IEX TotalV iew Detail and Summary
To: 04/19/06 Sprint C MG Daily
Shift: 0 All Day Time Utilizatio n Report Page: 4

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
Report Across Agent Moves: No Re port Agent Move s: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Ex ception Codes: No
Show Exception Code Details: No

Duration
Exception Code HH:MM Percent
32954 CERVANTES VICTOR
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36401 COATS JUSTINE
Open Time R99 5:00 100.00%
Total 5:00

36729 COOKS SOMMER
Open Time R99 3:00 100.00%
Total 3:00

30684 COTE CANDACE
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

1288 CROSS TIESHA
Open Time R99 7:15 100.00%
Total 7:15

36461 DANIEL SAMANTHA
Break R99 0:30 6.79%
Open Time R99 6:52 93.21%
Total 7:22

32555 DARLING DAVID
Break R99 0:30 5.41%
Open Time R99 8:45 94.59%
Total 9:15

36277 DAVIS ANNETTE
Break R99 0:30 6.25%
Open Time R99 7:20 91.67%
SO Entry R99 0:10 2.08%
Total 8:00

35843 DAVIS PAMELA
Break R99 0:30 6.25%

____________________

From: 04/19/06 IEX TotalV iew Detail and Summary
To: 04/19/06 Sprint C MG Daily
Shift: 0 All Day Time Utilizatio n Report Page: 5

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
Report Across Agent Moves: No Re port Agent Move s: No
Sort By Exception Code Group: No
Show Exception Code Group Totals For Selected Ex ception Codes: No
Show Exception Code Details: No

Duration
Exception Code HH:MM Percent
Open Time R99 7:05 88.54%
SO Entry R99 0:16 3.33%
Training R99 0:09 1.88%
Total 8:00

What I want to end up with is the following;

32954 CERVANTES VICTOR 7:45
36401 COATS JUSTINE 5:00
36729 COOKS SOMMER 3:00
30684 COTE CANDACE 7:30
1288 CROSS TIESHA 7:15
36461 DANIEL SAMANTHA 7:22
32555 DARLING DAVID 9:15
36277 DAVIS ANNETTE 8:00
35843 DAVIS PAMELA 8:00

Can anyone give me a hand with this? I would greatly appreciate it.

Thanks,

Wray

macropod · Apr 19, 2006

Hi Wray,

Try something like:

Code:

Sub Reformat()
Dim i As Long
With ActiveSheet
    For i = .UsedRange.Rows.Count To 1 Step -1
        If Len(Cells(i, 1)) = 0 Then .Rows(i).EntireRow.Delete
        If Asc(Cells(i, 1)) < 48 Then .Rows(i).EntireRow.Delete
        If Asc(Cells(i, 1)) > 57 Then
            If Left(Cells(i, 1), 5) <> "Total" Then
                .Rows(i).EntireRow.Delete
            End If
        End If
    Next
    For i = .UsedRange.Rows.Count To 1 Step -1
        With Cells(i, 1)
            .Replace What:=Chr(160), Replacement:=Chr(32)
            .Value = Application.Trim(Cells(i, 1).Text)
        End With
        If Left(Cells(i, 1), 5) = "Total" Then
            Cells(i, 1).Replace What:="Total", Replacement:=" "
            Cells(i - 1, 1).Value = Cells(i - 1, 1).Value & " " & Cells(i, 1).Value
            .Rows(i).EntireRow.Delete
        End If
    Next
End With
End Sub

Cheers

[MS MVP - Word]

Wray69 · Apr 20, 2006

well that gives me the list of names but I need to keep their total time also. Thank you for the reply.

Wray

macropod · Apr 20, 2006

Hi Wray,

With the data you posted the code returns:

32635 BARSKY CRISTY 7:30
17953 BASTIEN AMANDA 6:55
2876 BAUMGARTEN TIFFANY 7:00
18179 BEHL KERRI 8:00
7394 BERRY BRENDA 7:45
36632 BEST STANLEY 6:48
32594 BLANCHETTE FRANKIE 7:25
18333 BOROUGHF ALLAN 2:24
35679 BRADFORD LAKISHA 1:30
32972 BROWN DOUG E 7:45
30929 BROWN MARSHALL 8:00
7395 BRYAN PERRY 7:00
36670 BRYANT DIANNA 7:15
3272 BURGE TAMICHA 6:30
36456 BURTON LISA I 5:36
1060 CALDWELL INDIA 0:25
11680 CANNON HEATHER 6:30
32954 CERVANTES VICTOR 7:45
36401 COATS JUSTINE 5:00
36729 COOKS SOMMER 3:00
30684 COTE CANDACE 7:30
1288 CROSS TIESHA 7:15
36461 DANIEL SAMANTHA 7:22
32555 DARLING DAVID 9:15
36277 DAVIS ANNETTE 8:00
35843 DAVIS PAMELA 8:00

The total times are on the end of each line, almost exactly as you asked for (I only had one space between the names and times).

Cheers

[MS MVP - Word]

Wray69 · Apr 20, 2006

This is what I am getting;

32970 AKIN CHRYSTAL L
1333 ALEXANDER SHIMERE
14160 ARNOLD KIMBERLY
36668 ASHWORTH CRYSTAL
22011 ASTLE BRETTNEY
32635 BARSKY CRISTY
17953 BASTIEN AMANDA
2876 BAUMGARTEN TIFFANY
18179 BEHL KERRI
7394 BERRY BRENDA
36632 BEST STANLEY
32594 BLANCHETTE FRANKIE
18333 BOROUGHF ALLAN
35679 BRADFORD LAKISHA
32972 BROWN DOUG E
30929 BROWN MARSHALL
7395 BRYAN PERRY
36670 BRYANT DIANNA
3272 BURGE TAMICHA
36456 BURTON LISA I
1060 CALDWELL INDIA
11680 CANNON HEATHER
32954 CERVANTES VICTOR
36401 COATS JUSTINE
36729 COOKS SOMMER
30684 COTE CANDACE
1288 CROSS TIESHA
36461 DANIEL SAMANTHA
32555 DARLING DAVID
36277 DAVIS ANNETTE
35843 DAVIS PAMELA
1419 DAVIS TIMOTHY
32500 DAVIS-YOUNG ALEXIS
1 DEDMAN RUTH
36724 DENNING DONNA
44 DOMINGUEZ SARAH
33611 DOMINGUEZ-DAVILA S
12159 DOWNARD NINA
874 DUFFY CAROLYN
32963 DUNBAR TARA L
32973 DUNBAR THOMAS G
18847 DUNN ANDREW
30884 EDWARDS SHERRY
7117 EIDEN KELLY
35573 FREEMAN CERETA
36468 FRENCH LATORIA
32494 GANT PAIGE
6239 GARCIA LAURA
1939 GATES CORNELIUS
1949 GEORGE JOANN
36469 GILES TAMMY
32961 GIRAUD MARIE
36137 GREER THOMAS
33036 GUTIERREZ ALONZO
36471 HADNOTT KIMBERLY
33033 HAGERSTROM SHELBI
36635 HALL ROSSALYN
2842 HAMMITT SHAWNA
1340 HARDY JOSHUA
31270 HART CLARISSA
35900 HERNDON ARLENE
32957 HILSTAD LISA
36477 HODGE TONI G
33624 HOLT CARLY
36480 ISSAC TYISHA N
36481 JACKSON DENISE J
36482 JACKSON TARSH M
15165 JAMES KAREN
36484 JENKINS TOMEKA N
32950 JIMINEZ DENISE
36582 JOHNSON FALUN
857 JOHNSON GAIL
18581 JOHNSON RICCO
36627 JOHNSON ROSLYN
36613 JONES BRANDON
36661 JONES SHIRLEY
12117 JOPLIN WILLIAM
36488 JORDAN BERNADE M
36489 JOSEPH NIKISHA M
7896 KESLER JESSICA
1583 KING ANGELA
15183 KING TAKOYA
7 KIRBY VIRGINIA
7044 KNOWLES ALICE
32956 KOENEMANN SUZY
33035 LAMAS CASSI
3669 LAMB CHRISTINE
843 LEE JULIE
36591 LEWIS RONNIE
36598 LIBERIDHS DIMITRIOS
15228 LOREDO JULIA
32948 LOREDO MARLA
10772 LOSEE TRICIA
36731 LUCAS TANYA
9999 MABRY IRIS
36573 MARTIN IVAN
32545 MCCREE SHAWNA
1228 MCPIKE WILLENE
36663 MEANS TRACEY
32953 MONTEY ANGELA
32965 MOORE SHANNON M
36650 MOREIRA ISIDOR
167 MORRIS SOPHIA
375 NICEWONGER LINDA
32903 PAASCH JANICE
32905 PACKER-AKIN SALLY
9127 PASSIE CINDY
36677 PATTON SHALENA
1078 PENCE MAIDA
33032 PEREVERZINA ANNA
10892 PEREZ JOSIAH
459 PETERSON ROSEMARY
171 PETRAKIS KELLIE
36732 PETTY LEAH
4800 PHETTEPLACE RICK
36619 POINDEXTER NATASHA
841 POIRIER MARY
9121 POLNITZ DAKIESHA
11751 REYNOLDS LATASHA
36620 ROBERTS YVONNE
2426 ROGERS SUSAN
36734 SANDERS NIKISHA
36576 SANDERS OCTAVIA
32967 SANDERS PATRICIA S

I actually need to get the times in the next cell adjacent to the name. Also the data I have now does not have the number in front of the name.

Wray

macropod · Apr 20, 2006

Hi Wray,

Changing the data format doesn't help. Is the latest change, ie deleting the leading number, permanent?

As for the totals, I suspect your data has a tab between the total and the time. Is this correct? The data you posted only comes out with spaces. If you've got tabs, the solution is as simple as changing:

Code:

Cells(i - 1, 1).Value = Cells(i - 1, 1).Text & " " & Cells(i, [b]1[/b]).Text

to

Code:

Cells(i - 1, 1).Value = Cells(i - 1, 1).Text & " " & Cells(i, [b]2[/b]).Text

Cheers

[MS MVP - Word]

Wray69 · Apr 20, 2006

Thank you very much for gettin back to me, I mad the changes and I am now getting the following;

32970 AKIN CHRYSTAL L 9:45
1333 ALEXANDER SHIMERE 5:22
14160 ARNOLD KIMBERLY 2:30
36668 ASHWORTH CRYSTAL 7:31
22011 ASTLE BRETTNEY 8:00
32635 BARSKY CRISTY 7:30
17953 BASTIEN AMANDA 6:55
2876 BAUMGARTEN TIFFANY 7:00
18179 BEHL KERRI 8:00
7394 BERRY BRENDA 7:45
36632 BEST STANLEY 6:48
32594 BLANCHETTE FRANKIE 7:25
18333 BOROUGHF ALLAN 2:24
35679 BRADFORD LAKISHA 1:30
32972 BROWN DOUG E 7:45
30929 BROWN MARSHALL 8:00
7395 BRYAN PERRY 7:00
36670 BRYANT DIANNA 7:15
3272 BURGE TAMICHA 6:30
36456 BURTON LISA I 5:36

This is all in Column A though, I need the Name in Column A and the time in Column B. Also currently I am using the following formula to convert the time to deciaml;
=(B1-INT(B1))*24 Is ther a way to incorporate this into the MACRO as well?

Deleting the preceding number is going to be permenant, I am now recieving the data with out that number. If you can help me with that change as well it would be greatly appreciated.

I thank you for all your help, I can write a formula in Excel with the best of them but VBA confuses me all to be damned.

Thanks again,

Wray

macropod · Apr 20, 2006

hi Wray,

Give this a whirl:

Code:

Sub Reformat()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim TmpStr As String
With ActiveSheet
    For i = .UsedRange.Rows.Count To 1 Step -1
        With Cells(i, 1)
            .Replace What:=Chr(160), Replacement:=Chr(32)
            .Value = Application.Trim(Cells(i, 1).Text)
        End With
        If Cells(i, 1).Text = "Total" Then TmpStr = " " & Cells(i, 2).Value * 24
        If Len(Cells(i, 1)) = 0 Then
            .Rows(i).EntireRow.Delete
        ElseIf Left(Cells(i, 1), 1) = "_" Then
            .Rows(i).EntireRow.Delete
        ElseIf UCase(Cells(i, 1).Text) = Cells(i, 1).Text Then
            Cells(i, 1).Value = Cells(i, 1).Text & TmpStr
        Else
            .Rows(i).EntireRow.Delete
        End If
    Next
    For i = .UsedRange.Rows.Count To 1 Step -1
        TmpStr = ""
        l = 0
        For j = 1 To Len(Cells(i, 1))
            If Mid(Cells(i, 1), j + 2, 1) = " " Then
                l = l + 1
                k = j
            End If
        Next j
        If l > 2 Then
            For j = Len(Cells(i, 1)) To 1 Step -1
                If Mid(Cells(i, 1), j, 1) = " " Then
                    If j < k + 1 And l > 2 Then
                        TmpStr = Chr(160) & TmpStr
                        l = l - 1
                    Else
                        TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                    End If
                Else
                    TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                End If
            Next j
            With Cells(i, 1)
                .Replace What:=.Text, Replacement:=TmpStr
            End With
        ElseIf l < 2 Then
            For j = 1 To Len(Cells(i, 1))
                If Mid(Cells(i, 1), j, 1) = " " And j = k + 2 Then
                    If l = 1 Then TmpStr = TmpStr & "  "
                    If l = 0 Then TmpStr = TmpStr & "   "
                Else
                    TmpStr = TmpStr & Mid(Cells(i, 1), j, 1)
                End If
            Next j
            With Cells(i, 1)
                .Replace What:=.Text, Replacement:=TmpStr
            End With
        End If
        Cells(i, 1).TextToColumns DataType:=xlDelimited, Space:=True
    Next i
End With
End Sub

This code puts the surnames in column A, the given names in column, and the times in column C. Becasue some of the people have more than one given name and, for all I know, you might only have surnames for others, a simple text-to-columns conversion wasn't sufficient. So, the macro re-processes the data to pad it out where bits are missing or concatenate the given names if there are more than one. Only then is the text-to-columns conversion done.

The code also converts the times to decimal format, as requested.

Cheers

[MS MVP - Word]

Wray69 · Apr 21, 2006

WOW, This is super. Thank You for all your help. This is giving me exactly what I need. Do you know any good resources for learning VBA? I really need to get better at it since I have a feeling I will be getting more projects like this here soon.

Thanks again,

Wray

Wray69 · Apr 21, 2006

Grrrr, I just got a phone call and the preceding number IS going to be on the data I am recieving but it needs to be removed. Also I am going to have to have the enitr name in column A. Any chance you can help me out one more time?

Regards,

Wray

Wray69 · Apr 21, 2006

OK, this is what the Data is looking like now and they "promise that it wont change again. Can you please help me out one more time?

Exception Code HH:MM Percent

Agent Name Date: 4/19/2006

AKIN CHRYSTAL L
Break R99 0:25 4.27%
Open Time R99 5:20 54.70%
Training R99 4:00 41.03%
Total 9:45

ALEXANDER SHIMERE
Break R99 0:15 4.66%
Open Time R99 5:07 95.34%
Total 5:22

ARNOLD KIMBERLY
Break R99 0:15 10.00%
Open Time R99 2:15 90.00%
Total 2:30

ASHWORTH CRYSTAL
Break R99 0:30 6.65%
Open Time R99 7:01 93.35%
Total 7:31

ASTLE BRETTNEY
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

___________

/19/06 IEX TotalV iew Detail and Summary
/19/06 Sprint C MG Daily
All Day Time Utilizatio n Report Page: 2

EPAIR Sorted by: Name
: CST6CDT
ross Agent Moves: No Re port Agent Move s: No
xception Code Group: No
ption Code Group Totals For Selected Ex ception Codes: No
ption Code Details: No

Duration
Exception Code HH:MM Percent
BARSKY CRISTY
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

BASTIEN AMANDA
Break R99 0:30 7.23%
Open Time R99 6:25 92.77%
Total 6:55

BAUMGARTEN TIFFANY
Break R99 0:40 9.52%
Open Time R99 6:20 90.48%
Total 7:00

BEHL KERRI
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

BERRY BRENDA
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

BEST STANLEY
Open Time R99 6:48 100.00%
Total 6:48

BLANCHETTE FRANKIE
Break R99 0:30 6.74%
Open Time R99 6:55 93.26%
Total 7:25

BOROUGHF ALLAN
OT - Online X01 2:24 100.00%
Total 2:24

BRADFORD LAKISHA
Open Time R99 1:30 100.00%

___________

Thanks again,

Wray

macropod · Apr 21, 2006

Wray,

You're not helping here - data with/without numbers, 'Total' and Hrs the same/separate columns.

If you're getting data without numbers and the 'Total' and Hrs are in separate columns, the previous code should work just fine.

If you're getting data with numbers and the 'Total' and Hrs are in separate columns, try:

Code:

Sub Reformat()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim TmpStr As String
With ActiveSheet
    For i = .UsedRange.Rows.Count To 1 Step -1
        With Cells(i, 1)
            .Replace What:=Chr(160), Replacement:=Chr(32)
            .Value = Application.Trim(Cells(i, 1).Text)
        End With
        If Cells(i, 1).Text = "Total" Then TmpStr = " " & Cells(i, 2).Value * 24
        If Len(Cells(i, 1)) = 0 Then
            .Rows(i).EntireRow.Delete
        ElseIf Left(Cells(i, 1), 1) = "_" Then
            .Rows(i).EntireRow.Delete
        ElseIf UCase(Cells(i, 1).Text) = Cells(i, 1).Text Then
            Cells(i, 1).Value = Cells(i, 1).Text & TmpStr
        Else
            .Rows(i).EntireRow.Delete
        End If
    Next
    For i = .UsedRange.Rows.Count To 1 Step -1
        TmpStr = ""
        l = 0
        For j = 1 To Len(Cells(i, 1))
            If Mid(Cells(i, 1), j + 2, 1) = " " Then
                l = l + 1
                k = j
            End If
        Next j
        If l > 3 Then
            For j = Len(Cells(i, 1)) To 1 Step -1
                If Mid(Cells(i, 1), j, 1) = " " Then
                    If j < k + 1 And l > 3 Then
                        TmpStr = Chr(160) & TmpStr
                        l = l - 1
                    Else
                        TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                    End If
                Else
                    TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                End If
            Next j
        ElseIf l < 3 Then
            For j = 1 To Len(Cells(i, 1))
                If Mid(Cells(i, 1), j, 1) = " " And j = k + 2 Then
                    If l = 2 Then TmpStr = TmpStr & "  "
                    If l = 1 Then TmpStr = TmpStr & "   "
                Else
                    TmpStr = TmpStr & Mid(Cells(i, 1), j, 1)
                End If
            Next j
        Else
            TmpStr = Cells(i, 1).Text
        End If
            With Cells(i, 1)
                .Replace What:=.Text, Replacement:=Mid(TmpStr, InStr(TmpStr, " ") + 1, Len(TmpStr) - InStr(TmpStr, " "))
            End With
        Cells(i, 1).TextToColumns DataType:=xlDelimited, Space:=True
    Next i
End With
End Sub

In either case, if the 'Total' and Hrs are space separated, instead of in separate columns, change:

Code:

        If Cells(i, 1).Text = "Total" Then TmpStr = " " & Cells(i, 2).Value * 24

to

Code:

        If Left(Cells(i, 1), 5) = "Total" Then
            With Cells(i, 1)
                .Replace What:=.Value, Replacement:=Right(Cells(i, 1).Text, Len(Cells(i, 1).Text) - 6)
            End With
            TmpStr = " " & Cells(i, 1).Value * 24
            .Rows(i).EntireRow.Delete
        End If

Cheers

[MS MVP - Word]

Wray69 · Apr 21, 2006

Thanks again,

I am getting an illegal call or value on this lin;

.Replace What:=.Value, Replacement:=Right(Cells(i, 1).Text, Len(Cells(i, 1).Text) - 6)

I thank you for all the help and appologize for all the changes. Everytime I talk to them they want something different.

Wray

macropod · Apr 21, 2006

Hi Wray,

Are you sure the 'Total' and Hrs are in the same cell? If so, the code works fine for me. If not, the code will crash with "Run-time error '5': Invalid procedure call or argument", so don't use that variation.

Maybe it's time you told those doing the extract to stop mucking around and provide the data in a format YOU specify.

Cheers

[MS MVP - Word]

Wray69 · Apr 22, 2006

Hi Macropod,

Yeah, I really wish they would decide how they are going to give the data to me. I dont think they have much of a choice though. The program they are taking it from isnt too Excel friendly. I think it is just depending on how they import it into Excel once they get it. Here is what I get in each column from them.

Column A;

Exception Code

Agent Name

AKIN CHRYSTAL L
Break R99
Open Time R99
Training R99
Total

ALEXANDER SHIMERE
Break R99
Open Time R99
Total

ARNOLD KIMBERLY
Break R99
Open Time R99
Total

ASHWORTH CRYSTAL
Break R99
Open Time R99
Total

ASTLE BRETTNEY
Break R99
Open Time R99
Total

___________

/19/06
/19/06
All Day

EPAIR
: CST6CDT
ross Agent Moves: No Re
xception Code Group: No
ption Code Group Totals
ption Code Details: No

Exception Code
BARSKY CRISTY
Break R99
Open Time R99
Total

BASTIEN AMANDA
Break R99
Open Time R99
Total

BAUMGARTEN TIFFANY
Break R99
Open Time R99
Total

Column B;

Duration
HH:MM

Date:

0:25
5:20
4:00
9:45

0:15
5:07
5:22

0:15
2:15
2:30

0:30
7:01
7:31

0:30
7:30
8:00

IEX TotalV
Sprint C
Time Utilizatio

Sorted by:

port Agent Move

For Selected Ex

Duration
HH:MM

0:30
7:00
7:30

0:30
6:25
6:55

0:40
6:20
7:00

Column C;

Percent

4/19/2006

4.27%
54.70%
41.03%

4.66%
95.34%

10.00%
90.00%

6.65%
93.35%

6.25%
93.75%

iew Detail and Summary
MG Daily
n Report Page: 2

Name

s: No

ception Codes: No

Percent

6.67%
93.33%

7.23%
92.77%

9.52%
90.48%

What I need to end up with is the persons full name in Column A and then the decimal time in column B. Is this possible?

Thanks for all your help.

Cheers,

Wray

macropod · Apr 22, 2006

Hi Wray,

Yes, it'll be possible to get the full names in Column A and the times in Column B, but I'd rather not put any more effort into this until you and your colleagues can settle on the data format - your latest post looks quite different to what you've posted before. Any chance your colleagues can simply output the data as a text report formatted something like it was in your first post?

I'll be away for a few days, so hopefully you can have this sorted out by the time I get back.

Cheers

[MS MVP - Word]

Wray69 · Apr 22, 2006

It is a text file they import into excel, I got them to send me the txt file. When you get back if you can take a look at it, they dont care how I get it into excel as long as they end up with the full name in column A and the totoal decimal time in column B. I really do appologize about how big a pain this has all been.

Duration
Exception Code HH:MM Percent

ID Agent Name Date: 04/19/06

32970 AKIN CHRYSTAL L
Break R99 0:25 4.27%
Open Time R99 5:20 54.70%
Training R99 4:00 41.03%
Total 9:45

1333 ALEXANDER SHIMERE
Break R99 0:15 4.66%
Open Time R99 5:07 95.34%
Total 5:22

14160 ARNOLD KIMBERLY
Break R99 0:15 10.00%
Open Time R99 2:15 90.00%
Total 2:30

36668 ASHWORTH CRYSTAL
Break R99 0:30 6.65%
Open Time R99 7:01 93.35%
Total 7:31

22011 ASTLE BRETTNEY
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 2

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
32635 BARSKY CRISTY
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

17953 BASTIEN AMANDA
Break R99 0:30 7.23%
Open Time R99 6:25 92.77%
Total 6:55

2876 BAUMGARTEN TIFFANY
Break R99 0:40 9.52%
Open Time R99 6:20 90.48%
Total 7:00

18179 BEHL KERRI
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

7394 BERRY BRENDA
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36632 BEST STANLEY
Open Time R99 6:48 100.00%
Total 6:48

32594 BLANCHETTE FRANKIE
Break R99 0:30 6.74%
Open Time R99 6:55 93.26%
Total 7:25

18333 BOROUGHF ALLAN
OT - Online X01 2:24 100.00%
Total 2:24

35679 BRADFORD LAKISHA
Open Time R99 1:30 100.00%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 3

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Total 1:30

32972 BROWN DOUG E
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

30929 BROWN MARSHALL
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

7395 BRYAN PERRY
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

36670 BRYANT DIANNA
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

3272 BURGE TAMICHA
Open Time R99 6:30 100.00%
Total 6:30

36456 BURTON LISA I
Break R99 0:30 8.93%
Open Time R99 5:06 91.07%
Total 5:36

1060 CALDWELL INDIA
OT - Online X01 0:25 100.00%
Total 0:25

11680 CANNON HEATHER
Open Time R99 6:30 100.00%
Total 6:30

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 4

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
32954 CERVANTES VICTOR
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36401 COATS JUSTINE
Open Time R99 5:00 100.00%
Total 5:00

36729 COOKS SOMMER
Open Time R99 3:00 100.00%
Total 3:00

30684 COTE CANDACE
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

1288 CROSS TIESHA
Open Time R99 7:15 100.00%
Total 7:15

36461 DANIEL SAMANTHA
Break R99 0:30 6.79%
Open Time R99 6:52 93.21%
Total 7:22

32555 DARLING DAVID
Break R99 0:30 5.41%
Open Time R99 8:45 94.59%
Total 9:15

36277 DAVIS ANNETTE
Break R99 0:30 6.25%
Open Time R99 7:20 91.67%
SO Entry R99 0:10 2.08%
Total 8:00

35843 DAVIS PAMELA
Break R99 0:30 6.25%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 5

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Open Time R99 7:05 88.54%
SO Entry R99 0:16 3.33%
Training R99 0:09 1.88%
Total 8:00

1419 DAVIS TIMOTHY
Break R99 0:15 12.50%
Open Time R99 1:45 87.50%
Total 2:00

32500 DAVIS-YOUNG ALEXIS
Break R99 0:15 3.85%
Open Time R99 6:15 96.15%
Total 6:30

1 DEDMAN RUTH
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36724 DENNING DONNA
Break R99 0:30 6.64%
Open Time R99 7:02 93.36%
Total 7:32

44 DOMINGUEZ SARAH
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

33611 DOMINGUEZ-DAVILA S
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

12159 DOWNARD NINA
Break R99 0:30 6.90%
Open Time R99 6:24 88.28%
SO Entry R99 0:21 4.83%
Total 7:15

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 6

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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

874 DUFFY CAROLYN
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

32963 DUNBAR TARA L
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

32973 DUNBAR THOMAS G
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

18847 DUNN ANDREW
Break R99 0:30 7.11%
Open Time R99 6:32 92.89%
Total 7:02

30884 EDWARDS SHERRY
Open Time R99 4:18 100.00%
Total 4:18

7117 EIDEN KELLY
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

35573 FREEMAN CERETA
Break R99 0:30 7.11%
Open Time R99 6:19 89.81%
SO Entry R99 0:13 3.08%
Total 7:02

36468 FRENCH LATORIA
Break R99 0:30 7.59%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 7

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Open Time R99 6:05 92.41%
Total 6:35

32494 GANT PAIGE
Break R99 0:30 6.11%
OT - Online X01 0:11 2.24%
Open Time R99 7:30 91.65%
Total 8:11

6239 GARCIA LAURA
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

1939 GATES CORNELIUS
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

1949 GEORGE JOANN
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

36469 GILES TAMMY
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

32961 GIRAUD MARIE
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36137 GREER THOMAS
Break R99 0:30 5.71%
Open Time R99 8:15 94.29%
Total 8:45

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 8

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
33036 GUTIERREZ ALONZO
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36471 HADNOTT KIMBERLY
Break R99 0:30 7.08%
Open Time R99 6:34 92.92%
Total 7:04

33033 HAGERSTROM SHELBI
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

36635 HALL ROSSALYN
Break R99 0:30 8.17%
Open Time R99 5:37 91.83%
Total 6:07

2842 HAMMITT SHAWNA
Break R99 0:40 7.41%
Open Time R99 8:20 92.59%
Total 9:00

1340 HARDY JOSHUA
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

31270 HART CLARISSA
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

35900 HERNDON ARLENE
Break R99 0:30 5.26%
Open Time R99 9:00 94.74%
Total 9:30

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 9

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
32957 HILSTAD LISA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

36477 HODGE TONI G
Open Time R99 7:12 100.00%
Total 7:12

33624 HOLT CARLY
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36480 ISSAC TYISHA N
Break R99 0:30 6.59%
Open Time R99 6:16 82.64%
With Mgr - Supv R99 0:49 10.77%
Total 7:35

36481 JACKSON DENISE J
Break R99 0:30 6.45%
Open Time R99 6:03 78.06%
Training R99 1:12 15.48%
Total 7:45

36482 JACKSON TARSH M
Break R99 0:15 3.52%
Open Time R99 6:51 96.48%
Total 7:06

15165 JAMES KAREN
Break R99 0:30 8.33%
Open Time R99 5:30 91.67%
Total 6:00

36484 JENKINS TOMEKA N
Break R99 0:30 6.52%
Open Time R99 7:10 93.48%
Total 7:40

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 10

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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

32950 JIMINEZ DENISE
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

36582 JOHNSON FALUN
Open Time R99 6:56 100.00%
Total 6:56

857 JOHNSON GAIL
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

18581 JOHNSON RICCO
Open Time R99 6:15 100.00%
Total 6:15

36627 JOHNSON ROSLYN
Break R99 0:30 6.77%
Open Time R99 6:53 93.23%
Total 7:23

36613 JONES BRANDON
Open Time R99 3:45 100.00%
Total 3:45

36661 JONES SHIRLEY
Break R99 0:30 7.44%
Open Time R99 6:13 92.56%
Total 6:43

12117 JOPLIN WILLIAM
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

36488 JORDAN BERNADE M
Break R99 0:30 7.03%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 11

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Open Time R99 6:37 92.97%
Total 7:07

36489 JOSEPH NIKISHA M
Break R99 0:30 7.09%
Open Time R99 6:33 92.91%
Total 7:03

7896 KESLER JESSICA
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

1583 KING ANGELA
Break R99 0:30 6.67%
Open Time R99 6:40 88.89%
SO Entry R99 0:07 1.56%
Training R99 0:13 2.89%
Total 7:30

15183 KING TAKOYA
Break R99 0:15 5.26%
Open Time R99 4:30 94.74%
Total 4:45

7 KIRBY VIRGINIA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

7044 KNOWLES ALICE
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

32956 KOENEMANN SUZY
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 12

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
33035 LAMAS CASSI
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

3669 LAMB CHRISTINE
Open Time R99 4:45 100.00%
Total 4:45

843 LEE JULIE
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

36591 LEWIS RONNIE
Break R99 0:30 8.17%
Open Time R99 5:37 91.83%
Total 6:07

36598 LIBERIDHS DIMITRIOS
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

15228 LOREDO JULIA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

32948 LOREDO MARLA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

10772 LOSEE TRICIA
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36731 LUCAS TANYA
Open Time R99 6:23 100.00%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 13

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Total 6:23

9999 MABRY IRIS
Open Time R99 2:38 100.00%
Total 2:38

36573 MARTIN IVAN
Break R99 0:30 10.00%
Open Time R99 4:30 90.00%
Total 5:00

32545 MCCREE SHAWNA
Break R99 0:30 7.69%
Open Time R99 6:00 92.31%
Total 6:30

1228 MCPIKE WILLENE
Break R99 0:15 4.17%
Open Time R99 5:45 95.83%
Total 6:00

36663 MEANS TRACEY
Break R99 0:30 6.79%
Open Time R99 6:52 93.21%
Total 7:22

32953 MONTEY ANGELA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

32965 MOORE SHANNON M
Break R99 0:15 3.57%
Open Time R99 2:45 39.29%
Training R99 4:00 57.14%
Total 7:00

36650 MOREIRA ISIDOR
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 14

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
Total 7:45

167 MORRIS SOPHIA
Break R99 0:15 7.14%
Open Time R99 3:15 92.86%
Total 3:30

375 NICEWONGER LINDA
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

32903 PAASCH JANICE
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

32905 PACKER-AKIN SALLY
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

9127 PASSIE CINDY
Open Time R99 6:00 100.00%
Total 6:00

36677 PATTON SHALENA
Open Time R99 3:53 100.00%
Total 3:53

1078 PENCE MAIDA
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

33032 PEREVERZINA ANNA
Break R99 0:40 6.84%
Open Time R99 9:05 93.16%
Total 9:45

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 15

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
10892 PEREZ JOSIAH
Open Time R99 2:00 100.00%
Total 2:00

459 PETERSON ROSEMARY
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

171 PETRAKIS KELLIE
Break R99 0:30 6.67%
Open Time R99 7:00 93.33%
Total 7:30

36732 PETTY LEAH
Break R99 0:30 6.73%
Open Time R99 6:56 93.27%
Total 7:26

4800 PHETTEPLACE RICK
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

36619 POINDEXTER NATASHA
Break R99 0:15 3.66%
Open Time R99 6:14 91.22%
Training R99 0:21 5.12%
Total 6:50

841 POIRIER MARY
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

9121 POLNITZ DAKIESHA
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 16

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
11751 REYNOLDS LATASHA
Break R99 0:30 6.67%
Open Time R99 6:36 88.00%
SO Entry R99 0:24 5.33%
Total 7:30

36620 ROBERTS YVONNE
Break R99 0:30 7.85%
Open Time R99 5:52 92.15%
Total 6:22

2426 ROGERS SUSAN
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

36734 SANDERS NIKISHA
Break R99 0:15 3.80%
Open Time R99 6:20 96.20%
Total 6:35

36576 SANDERS OCTAVIA
Break R99 0:30 7.39%
Open Time R99 6:16 92.61%
Total 6:46

32967 SANDERS PATRICIA S
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

1010 SATERFIELD ALVINA
Open Time R99 6:15 100.00%
Total 6:15

33025 SCHUTT KALEB
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 17

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
36736 SCOTT JESSIE
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

36622 SIMON COVANA
Break R99 0:30 6.79%
Open Time R99 6:52 93.21%
Total 7:22

32081 SMITH COREY
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

36507 SMITH KRISTAL L
Break R99 0:30 6.73%
Open Time R99 6:56 93.27%
Total 7:26

36679 SMITH ROBIN
Break R99 0:30 6.98%
Open Time R99 6:40 93.02%
Total 7:10

2180 SURRATT AIMEE
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

31126 TAGHAN STEPHAN
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

36561 TAYLOR ANGELA
Break R99 0:30 8.22%
Open Time R99 5:35 91.78%
Total 6:05

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 18

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
32476 TAYLOR APRIL
Break R99 0:15 5.00%
Open Time R99 4:41 93.67%
SO Entry R99 0:04 1.33%
Total 5:00

36572 TAYLOR DAISY
Break R99 0:30 6.90%
Open Time R99 6:45 93.10%
Total 7:15

36510 THOMAS KESHA R
Break R99 0:30 6.85%
Open Time R99 6:48 93.15%
Total 7:18

287 TIMBLIN AMBER
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

221 TIPTON STEVEN
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

1358 TUNKS TYSHELL
SO Entry R99 0:01 100.00%
Total 0:01

32910 WAGNER JESSICA
Break R99 0:30 7.14%
Open Time R99 6:30 92.86%
Total 7:00

36571 WALKER CALVIN
Break R99 0:30 7.85%
Open Time R99 5:52 92.15%
Total 6:22

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 19

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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
486 WANDLING DONNA
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

16197 WEBB MARIA
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

35968 WEST SHIRRELL
Break R99 0:45 6.25%
Open Time R99 11:15 93.75%
Total 12:00

1285 WESTER ADAM
Break R99 0:15 5.00%
Open Time R99 4:45 95.00%
Total 5:00

2425 WILHITE LORI
Break R99 0:40 7.02%
Open Time R99 8:50 92.98%
Total 9:30

36422 WILLIAMS MARJORIE
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

16098 WILLIAMSON JARED
Open Time R99 1:00 100.00%
Total 1:00

12647 WILLY CHRIS
Break R99 0:30 6.90%
Open Time R99 6:20 87.36%
SO Entry R99 0:10 2.30%
With Mgr - Supv R99 0:15 3.45%
Total 7:15

____________________

From: 04/19/06 IEX TotalView Detail and Summary
To: 04/19/06 Sprint CMG Daily
Shift: 0 All Day Time Utilization Report Page: 20

CT: 700 REPAIR Sorted by: Name
Time Zone: CST6CDT
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

20105 WISNER BRYAN
Break R99 0:30 8.15%
Open Time R99 5:38 91.85%
Total 6:08

36519 WOODARD ADELE M
Break R99 0:30 7.46%
Open Time R99 6:12 92.54%
Total 6:42

31111 WOODRUFF DEONNA
Break R99 0:30 6.25%
Open Time R99 7:30 93.75%
Total 8:00

36715 ZZ* MARTIN K 3/06
Break R99 0:30 6.45%
Open Time R99 7:15 93.55%
Total 7:45

Summary Data For: Date: 04/19/06

Total Agents: 154
Break R99 63:05 5.92%
OT - Online X01 3:00 0.28%
Open Time R99 970:30 91.10%
SO Entry R99 1:46 0.17%
Training R99 25:55 2.43%
With Mgr - Supv R99 1:04 0.10%
Total 1065:20

Thank You,

Wray

macropod · Apr 26, 2006

Hi Wray,

See if this does what you want"

Code:

Sub Reformat()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim TmpStr As String
With ActiveSheet
    For i = .UsedRange.Rows.Count To 1 Step -1
        With Cells(i, 1)
            .Replace What:=Chr(160), Replacement:=Chr(32)
            .Value = Application.Trim(Cells(i, 1).Text)
        End With
        If Len(Cells(i, 1)) = 0 Then
            .Rows(i).EntireRow.Delete
        ElseIf Left(Cells(i, 1), 1) = "_" Then
            .Rows(i).EntireRow.Delete
        ElseIf Cells(i, 1).Text = "Total" Then
            TmpStr = " " & Cells(i, 2).Value * 24
            .Rows(i).EntireRow.Delete
        ElseIf Left(Cells(i, 1), 5) = "Total" Then
            With Cells(i, 1)
                .Replace What:=.Value, Replacement:=Right(Cells(i, 1).Text, Len(Cells(i, 1).Text) - 6)
            End With
            TmpStr = " " & Cells(i, 1).Value * 24
            .Rows(i).EntireRow.Delete
        ElseIf UCase(Cells(i, 1).Text) = Cells(i, 1).Text Then
            Cells(i, 1).Value = Cells(i, 1).Text & TmpStr
        Else
            .Rows(i).EntireRow.Delete
        End If
    Next
    For i = .UsedRange.Rows.Count To 1 Step -1
        TmpStr = ""
        l = 0
        For j = 1 To Len(Cells(i, 1))
            If Mid(Cells(i, 1), j + 1, 1) = " " Then
                l = l + 1
                k = j
            End If
        Next j
        If l > 2 Then
            For j = Len(Cells(i, 1)) To 1 Step -1
                If Mid(Cells(i, 1), j, 1) = " " Then
                    If j < k + 1 And l > 2 Then
                        TmpStr = Chr(160) & TmpStr
                        l = l - 1
                    Else
                        TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                    End If
                Else
                    TmpStr = Mid(Cells(i, 1), j, 1) & TmpStr
                End If
            Next j
        ElseIf l < 2 Then
            For j = 1 To Len(Cells(i, 1))
                If Mid(Cells(i, 1), j, 1) = " " And j = k + 2 Then
                    If l = 1 Then TmpStr = TmpStr & "  "
                    If l = 0 Then TmpStr = TmpStr & "   "
                Else
                    TmpStr = TmpStr & Mid(Cells(i, 1), j, 1)
                End If
            Next j
        Else
            TmpStr = Cells(i, 1).Text
        End If
            With Cells(i, 1)
                .Replace What:=.Text, Replacement:=Mid(TmpStr, InStr(TmpStr, " ") + 1, Len(TmpStr) - InStr(TmpStr, " "))
            End With
        Cells(i, 1).TextToColumns DataType:=xlDelimited, Space:=True
    Next i
End With
End Sub

Cheers

[MS MVP - Word]

Wray69 · Apr 26, 2006

Welcome back Macropod,

Does'nt seem to be working out for me. How are you importing it into excel? Fixed width? copy and paste? Im sure that is where I am going wrong with your latest code. I tried seeral different ways.

Thanks,

Wray

macropod · Apr 26, 2006

Hi Wray,

I simply copied the data from your penultimate post and pasted it into Excel - each line goes into a single cell.

Cheers

[MS MVP - Word]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Organizing data in Excel using VBA 1

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor