Expert help- design macro to format non-standard excel report

Hello All,

I require the help of an expert! This is sort of a hail mary.. so here goes:

We have a dated call logging system at our call center that provides us with an overview of agent login and logout information. My goal is to get the report formatted in a way that I could pivot the data and look into agent login/logout details. In short the format of the report doesn't lend it self to be pivoted. The system has reached 'End of life' and there's no one to support or to redesign the report. So I wanted to know if was possible to just write a script to massage the data into a simple table that could then be 'pivoted'

Script would do the following:
Add column headers: DATE RAWTIME TIMEBIN STATUS AGENT NOTREADY TOTALLOGIN
Parse line of text that starts with 'Agent Login' and pull agent # and agent name (everything after the ':' )
Delete Rows that start with 'Agent Login' and 'Initial Login'
After first date fill out blank cells with the same date
Fill out cells with time rounded into 30 minute bins in column C
Calculate time between ready and not ready- put results in F column
Put appropriate agent information in column E

This is something I was trying to script but I'm stumped. I currently do all this manually but I sometimes make mistakes and would like to expedite the process.

I've included a sample workbook with 2 sheets. Sheet 1 shows the raw data, and 'RESULTS' show what the report should look like after.

please assist!

Getting a subscript out of range on this line of code

Please Login or Register  to view this content.

I created a module in my personal.xls and running it from there.

Where should I be running this?

Thank you for the fast response Mike! I'm excited to see if this works.

Excellent it worked in the sheet you attached. However I'm trying it on a larger sample on different worksheet and I'm getting the subscript out of range. How would you like to proceed?

See attached

Excellent! That's working!

1 issue I did see is the 'AGENT' column on the results need a 'TRIM' of the leading space. So I did the following:

Please Login or Register  to view this content.

That seemed to work- not sure if that was the correct thing to do.

Now for the last part- Do you have any suggestions on how to calculate the time someone spends 'Not Ready'

P.S. Clean up your private messages-- can't send you a response!