New Here.. Need help with Data Extraction in Excel

[Exhorter]

Let me start by saying thanks for any help you guys can give. Here is my issue:

Sample Data:

Please Login or Register  to view this content.

I need someway to look at this type data, and find the agent out of a list of 50+ agents. Once that agent is found, then I need to find the total Min of adherence. The data I need would look similar to this:

Jackson, April 80.03
Jackson, Theresa 84.52

This data is dynamic as it changes often and the categories also change.

Any Ideas?

[oeldere]

http://www.excelforum.com/the-water-...-question.html

[Exhorter]

AD_Report.xlsx

Gotcha. here is a portion of the file. You should be able to see from here what I am looking at.

[LJMetzger]

Hi Exhorter and Welcome to ExcelForum,


Your problem can be solved with a VBA macro as follows:
1. Name: Read each data line until the 'Agent:' token is found at the beginning of the line. From that the Name can be extracted.
2. Scan until the 'Training' token is found at the beginning of the line. The next line should contain the 'Total' token at the beginning of the line. Extract the number that contains '%' on that line.
3. Repeat 1 and 2 until there is no more data.

It is unclear whether your data is in an Excel file or in a text file. If it's in an Excel file please upload a sample file if you want further assistance. If the data is in a text file, you have already provided the data.

In either case, please let us know if you need additional help.

Lewis

PPS. Thanks for posting the file. Please let us know where you want the results.

[Exhorter]

Hi Exhorter and Welcome to ExcelForum,


Your problem can be solved with a VBA macro as follows:
1. Name: Read each data line until the 'Agent:' token is found at the beginning of the line. From that the Name can be extracted.
2. Scan until the 'Training' token is found at the beginning of the line. The next line should contain the 'Total' token at the beginning of the line. Extract the number that contains '%' on that line.
3. Repeat 1 and 2 until there is no more data.

It is unclear whether your data is in an Excel file or in a text file. If it's in an Excel file please upload a sample file if you want further assistance. If the data is in a text file, you have already provided the data.

In either case, please let us know if you need additional help.

Lewis

Thank you Lewis. I have uploaded an Excel file of the data. I am quite new to VBA but have a general understanding of what you are talking about. Would I need to post in a separate place for help with the macro?

[LJMetzger]

You are in the correct place. Please let us know where you want the results to be posted (e.g. where in the Excel file).

Lewis

[Exhorter]

It doesn't matter to me. Anywhere is fine as I am going to using this for a larger spreadsheet.

[Exhorter]

My plans are to use the Macro via a button that I will put into the spreadsheet. I will be loading new data frequently with a simple copy and paste.

[LJMetzger]

See if the attached file does what you want. Please let me know if you need any changes.

Lewis

Code follows:

Please Login or Register  to view this content.

[Exhorter]

You are officially my hero!

Thank you Lewis!

[LJMetzger]

My pleasure. Anything for the home of Bart Starr.

[Exhorter]

My pleasure. Anything for the home of Bart Starr.

HAHA.. not many PPL would know that. Nice Job.

I do have one more question though. I have been playing around with the code and spreadsheet and would love to add a column in the output that has the Total Mins of Computer Problems.

Computer problems will not be listed for each person so a 0 would need to be in its place if computer problems was not listed at all.

Is this too much to ask?

Thanks a Ton. I am attempting to break apart your code so I can learn what you have done. thanks again.

[Exhorter]

I actually spoke to soon here. I was able to figure it out myself. Thanks Again for all the help.

[LJMetzger]

It was my goal to teach you to fish, not to feed you. It seems like I succeeded.

I'm glad everything worked out. Please ask if you need any more help.

Lewis