Data Sorting Macro - Please help

[paconovellino]

Hello, Im new to macros and I would really appreciate your help.

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

The sample data can be found on this link https://www.dropbox.com/s/9mxs1wonu5...ro%20Data.xlsx

Thanks!!!

[Cofad]

Have you tried the "text to columns" tool? I don't know if it will do what you want, but maybe it's worth a shot.

[paconovellino]

Hi Cofad. Actually I did, but it didnt work

[Cofad]

Seems to me like a good first attempt would be to transpose all the data sets into columns and then to delete the unnecessary columns?

[Leith Ross]

Hello paconovellino,

The attached workbook organizes the data by date, ID, agent name, task, and time. The data is then output to Sheet3 starting in row 2. Row 1 has headers. There are no blank rows in data. There is a button at the top of Sheet3 to run the macro.

The macro is very fast. The average execution time on my computer is 0.21 seconds. Here is the macro code. There are 2 standard VBA modules used in this project.

Fast_Find Module Macro

Please Login or Register  to view this content.

Module1 Macro

Please Login or Register  to view this content.