Organizing data in Excel using VBA

[Wray69]

I know this is not a VBA formula but I have alwyas got my most difficult questions here. 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

 

[SkipVought]

wray,

since this will be an intensive VBA solution, please post in VBA Visual Basic for Applications (Microsoft) forum707.

BTW, do you have relatives in Brewster NY?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Wray69]

Not sure just moved to Illinois from Watertown NY. I have moved it to the VBA forum.

Thanks,

Wray

 

[KenWright]

Why the need for VBA? In a helper column you could use a simple IF or ISNUMBER statement to see whether or not the fisrt few digits are numeric, then just filter the rest and delete them.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

I suppose that you could, as Ken suggested.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}